Import last Excel ws to Access table

[amg0657]

Hi,

I am using the DoCmd.TransferSpreadsheet method to import an Excel worksheet
to an Access table. However, the worksheet I want to import is the last
sheet in the file. Using the DoCmd.TransferSpreadsheet method imports the
first worksheet. How do I get around this so that I import the last
worksheet into an Access db? Please advise. Thanks.


AMG

 

[Dirk Goldgar]

Hi,

I am using the DoCmd.TransferSpreadsheet method to import an Excel
worksheet
to an Access table. However, the worksheet I want to import is the last
sheet in the file. Using the DoCmd.TransferSpreadsheet method imports the
first worksheet. How do I get around this so that I import the last
worksheet into an Access db? Please advise. Thanks.

Do you know the name of the worksheet? If you do, you can import the sheet
by specifying its name, followed by "$", in the Range argument of
TransferSpreadsheet, like this:

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"ImportedTableName", _
"C:\Users\Dirk\Documents\MyExcelFile.xls", _
True, _
"Sheet3$"

If you don't know the name of the sheet, I think you may have to use
automation to create an Excel.Application object, use that to open the
workbook and find out the sheet name, then close the workbook and Excel
application, and only then import the sheet using the name you discovered.

 

[amg0657]

Yes, I typed the name of the sheet and it worked. Thanks.