Import form Excel

  • Thread starter Thread starter Chad
  • Start date Start date
C

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
 
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!
 
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
 
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!"
 
Back
Top