Import form Excel

[Chad]

Is there a way to import data (in VBA) from an Excel file
making the data on each worksheet become a table?

Example:
I have an Excel file that has 3 worksheets named Table1,
Table2, and Table3 for example's sake. The name of the
Excel file is backup.xls.

In my Access data base I have a function set up with the
code:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table1", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True

This code imports the data from the 1st worksheet into a
table named Table1 but I am not sure how to import the
data from the other worksheets within the file as Table2
and Table3 respectively.

Any help would be greatly appreciated.

Thanks,
Chad

 

[Ken Snell]

Use the Range argument to specify the second or third worksheet:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table1", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True, _
Range:=WorksheetName!

 

[Chad]

Ken,
I tried using the range argument but it wasnt working
for some reason (I still always imported the first
worksheet.) Here is my code for importing the first 2
worksheets into tables, can you please let me know if I
did anything wrong?

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table1", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True, _
Range:=Table1

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table2", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True, _
Range:=Table2

Thanks again for your help,
Chad

 

[Ken Snell]

You need to put an ! at the end of the sheet name. Also, I had omitted the "
marks that should delimit the name.

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table1", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True, _
Range:="Table1!"

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="Table2", _
FileName:="F:\Desktop\backup.xls", _
hasfieldnames:=True, _
Range:="Table2!"