Problems importing multiple worksheets

[ibgary]

I have an Excel spreadsheet that may have up to 12 worksheets in it, depending on the month of the year. I would like to import all available sheets into Access 2002.

Using DoCmd.TransferSpreadsheet , , "GMImports", "I:\PSUProgs\Data\Aparts\PartsBucket\reports.xls", True will give me only the first sheet. If I place the sheet name (January 2004) + "!", in the range position, it will import the designated sheet without problem. However I really want all records from all worksheets.

The help files indicate TransferSpreadsheet should import all sheets by leaving the range blank. Does that mean an empty string, or just leave it off ?

Any ideas I am doing wrong?

Thanks, ibgary

 

[PHV]

I'm afraid you have to do 12 cycles of import into a temp table + append query to GMImports.
Or you may consider using OLE Automation to read the workbook.
Or you may play with ADO.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ibgary]

I may have to use OLE to "read the workbook" first, as the looping through the spreadsheet seems to bomb out when I try to import a months worksheet that does not exist yet.

Other than that, it does seem to append to the same table just fine.

Thanks for your advice.

ibgary