1

I am trying to streamline the production of a document that has to be created on a regular basis. There are tasks associated with detailed instructions on how to complete them, and each of these has a specific ID number that is reused across various projects. I sucked up as many of these task IDs as I could into an access database.
For each project, somebody else pulls a list of all of the required tasks from another database that I cannot access and puts them into an Excel workbook with a standard format. For each project, I need to:

  • Pull in all relevant tasks by ID number
  • Compare them to the standard list in my database which has the steps to complete the task and some other info
  • Generate a document with a specific format using the specific information about the tasks

Writing a macro in excel seemed like a lot of work, but the best bet, but I can't be sure that the access database will always stay in the same place that it belongs. Since the database is the consistent item across all projects, I thought about writing a macro in Access and having a dialog asking where the spreadsheet is would work, but using a table to compare the two lists seemed more complex than simply having a comparison sheet generated within excel. I didn't really consider writing a macro within Word, though that is a possibility too.

I am trying to find the most logical way to create this setup. Using 3 different office programs seems fairly convoluted, but I am limited in the software that I have available to accomplish this:

  • Office 2007
  • Beyond Compare
  • The other programs installed (I can't install additional software) don't seem remotely relevant to the task

Does anyone have any idea how to go about this?

3 Answers 3

1

If you had Crystal Reports, you could join the Excel table to the Access tables; then print 2 reports, one with data match on ID, and the other report to list records without ID matches. Formatting is awsome. I write reports where I join Excel files to an Oracle database for reporting. You should be able to join the Excel file to ACCESS and create the same type of reports assuming both have the ID fields to match on.

0

Have you considered using an ODBC connection to link the two datasets?

Google results for ODBC and Excel

If your native work environment is the database, and you can work on automating the input of those new fields of data, you can use simple queries to select the unmatched (new) data and work from there. It's a shame that you can't simply punch out of the first database to your own, or have the first set of data exports simply export a CSV of new or changed data.

On either side, you can append the new data, and compare it to the existing (with queries in Access, or a Pivot Table in Excel). If you create a new column/field to act as a simple boolean flag, you can differentiate between datasets and merge them into the same table/document. Alternatively apply a code for weekly (or whatever period) imports so you can use this "flag" field for future queries.

The pivot table would be my first port of call, as you can work faster on the scoping stage than with database queries, and you can view your reporting/comparison requirements in the one view.

If you can "wash" your data to anonymise it, please post some examples for more specific solutions.

0

You can get at the excel data as a recordset, which will allow easy comparison

Dim xldb As New adodb.Connection
Dim xlcmd As New adodb.Command
Dim XL As New adodb.Recordset

With xldb
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;ReadOnly=True"";"
    .Open
End With

Set xlcmd.ActiveConnection = xldb
xlcmd.CommandType = adCmdText
xlcmd.CommandText = "Select * from [Sheet1$]"
XL.CursorLocation = adUseClient
XL.CursorType = adOpenDynamic
XL.LockType = adLockOptimistic
XL.Open xlcmd

now you can use the XL recordset, and search/step through it, and place the info into your database

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .