Hi JP
I'm not sure what you mean by "without doing the import by tab". It seems
to me that is exactly what you need to do.
You can link a given worksheet (tab) in an Excel workbook like this:
DoCmd.TransferSpreadsheet _
acLink, acSpreadsheetTypeExcel9, "TempExcel", _
sFileName, True, sWorksheet & "$"
Here, sFileName is the full path to the Excel file and sWorksheet is the
name of the worksheet. Note that it is necessary to append a dollar sign.
I would put this code in a loop, executing once for each worksheet, and
follow up each TransferSpreadsheet with an append query to add records from
the TempExcel table to your destination table, then delete TempExcel and
repeat.
If the names of the worksheets are pre-known then you can either put them in
an array in your code or read them from a table. If you simply want to
import every worksheet in the workbook, then you can use automation to open
the workbook first and read the names of all the worksheets. Post back if
you need any help with this.
