Import Excel file with multiple sheets into single table

[GraeagleBill]

Given an Excel file with multiple sheets, how does one import all sheets into a single table? Using the import wizard I only get the first sheet, whereas I'd like to keep importing each sheet sequentially until all sheets have been imported into the table.



Can this be done, or does one have to do each sheet and then merge the tables?

[ranman256]

1. you can manually run thru the import for each sheet,
or

2. is the #sheets always the same? if so,

a.save the workbook to the same place everytime, like: c:\temp\File2Import.xlsx
b. manually link each sheet as an external table, ie: tSheet1, tSheet2
c. next make an append query for each sheet: qaImportSheet1, qaImportSheet2
d. put all queries into a macro: mImportAllXLsheets
e run macro.

then next time,
1. save the workbook to c:\temp\File2Import.xlsx
2. run macro.
done.

[Welshgasman]

I would use VBA and walk through each sheet and import with TransferDataBase.

[GraeagleBill]

In the current case, walking through each sheet turns out to be the simplest method and leads easily to the looping through the tables collection find what's needed.
Thanks,
Bill