Getting mixed tabular & non-tabular data from Excel into Access

Question

My Access programming is a little rusty, & I've never worked with Excel files all that much.

I have a requirement to bring data from Excel spreadsheets into Access 2007. These spreadsheets have a fixed (predictable) format, but it includes a "header area" where I need to read single data items from specific cells, followed by a mass of tabular data (~500 rows in the one sample I've seen so far). I will be processing all of this into a set of tables that are normalized quite differently from the flat layout of the spreadsheet.

I know how to open an ADO recordset on the tabular data, and it should work fairly well for my purposes. I also figure that I can reference the Excel object model and open the sheets through Automation to get the "header area" data items.

My question is this: since I have to (I think) use the Automation approach for the "header area", am I better off just leaving it open in this mode to move on to the tabular data (with cell/range navigation), or closing that mode & going over to ADO? I suspect it's the latter--and I'd be more comfortable with it--but I don't want to do the wrong thing just because it's more familiar.

Edit It seems I wasn't clear that I need to build this capability into the "application", as something that a user can repeat down the line. I'm assured that I can trust the format of the spreadsheet (though I'll include error trapping for graceful failure if that turns out to be false). These spreadsheets are "official design documents" for hardware, and my app needs to handle bringing in new &/or updated ones to track the things that are described in the tabular data in ways that the flat Excel format diesn't allow for.

Answer 1

I would do it all via Automation. Why have two separate processes where one will do? After you've read the header information reading the tabular information will be quite easy.

Answer 2

Of those two options, I would choose the second simply because I find it more convenient to work with an ADO recordset. It should be fairly simple if you can assign a named range to your spreadsheet's tabular data.

Edit: If your spreadsheet includes field names, the recordset approach would be less prone to break due to spreadsheet changes such as one or more new columns inserted before or between the existing columns or a re-ordering of the existing columns.

But actually, I think the TransferSpreadsheet Method might be more convenient. You can specify the spreadsheet range as a named range or by cell address as in this example from the linked page:

DoCmd.TransferSpreadsheet acImport, 3, _
    "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

Also, you can choose between importing the spreadsheet range directly into an Access table, or linking to the range as a "virtual" table ... whichever best meets your application's needs.

Edit2: Creating a link (acLink instead of acImport) with TransferSpreadsheet would allow you to execute SQL statements against the link table:

INSERT INTO DestinationTable (field1, field2, field3)
SELECT foo, bar, bat FROM LinkedTable;

Answer 3

I inherited an application back in mid-2000 that was built to import Excel spreadsheets that were basically reporting output from MYOB (an accounting program). What had been done was to simply create a template table that had all the columns necessary to accomodate the report, using text data type for all columns. Then the non-data rows were filtered out and processed into the eventual destination table.

It's not elegant, and doesn't require a lot of programming, though the implementation I inherited used a dedicated temp table for each report layout that was being imported. You could easily replace all of those with a single table with 100 text columns of 255 (or memo fields, for that matter, if that was a requirement), and just re-use it.

I'm not sure if I'd recommend it or not, but it really is quite easy without requiring much in the way of code.

Answer 4

If the header information is really complicated, this can simplify your coding work:

1. In the official design Excel file, create a hidden tab.
2. In that tab, make a 1-row table connecting to all the header elements you're interested in. (i.e. set row 1 column 1 to "Document#" and row 2 column 1 to Sheet1:A1)
3. Then you can re-use the same VBA procedure to import both your tabular data and your header data.