Import last Excel ws to Access table

  • Thread starter Thread starter amg0657
  • Start date Start date
A

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
 
amg0657 said:
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.
Click to expand...


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.
 
Back
Top