Importing Excel Workbook - Multiple Sheets

[Guest]

I have an Excel workbook with multiple sheets (22) that I would like to
import into Access Tables for manipulation. I'd like to load them all to the
same table if possible, so long as I know what the name of the sheet the
records came from.
Is there any systematic way of doing this? I don't want to manually import
the data, and I would rather not write 22 transferspreadsheet expressions.

 

[Ken Snell [MVP]]

You can import one sheet at a time with a single TransferSpreadsheet action.

You could do this via VBA code, still having to do the TransferSpreadsheet
action 22 times in code, but you then could insert the worksheet name to a
field in the table.

The concept would be to do the import in a loop in VBA code, storing the
worksheet name in a variable during that loop. Then while still in the first
loop, run an update query on the table that received the data to update all
records where the worksheet name field is empty with the worksheet name that
you have in the variable. Then loop again for the next worksheet, etc.

 

[John Nurick]

Hi,

From the way you mention TransferSpreadsheet I assume you're reasonably
familiar with VBA.

If you go to http://groups.google.com and search for
"drink soup audibly" group:microsoft.public.excel.programming
you'll find a GetWSNames() function which returns an array containing
the name of every worksheet in the workbook.

Iterate through this constructing and executing a SQL append query for
each worksheet. The completed SQL string will be like this, if the Excel
table has column headers (MyCol1, MyCol2...) that match the Access field
names:

INSERT INTO
MyTable
(SourceSheet, MyCol1, MyCol2)
SELECT
XXX As SourceSheet
MyCol1,
MyCol2
FROM
[Excel 8.0;HDR=Yes;Database=D:\Folder\File.xls;].[XXX$]
;

where XXX is the name of the worksheet, and SourceSheet the field you
want it to go in.

If the column headers on the worksheet don't match those in the table,
alias them in the SELECT clause, e.g.
XXX AS SourceSheet
ExcelCol1 AS MyCol1
ExcelCol1 AS MyCol1
If the worksheet doesn't have column headers, "set HDR=No;" in the
connect string and then
...
F1 AS MyCol1
F2 AS MyCol2