Exporting Excel workbook to Access table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to export an Excel file (4 worksheets) to a single Access table.

The code I use is the following:

DoCmd.TransferSpreadsheet acImport, , "DataImport", FileName, False,
"Sheetname1"
DoCmd.TransferSpreadsheet acImport, , "DataImport", FileName,
False, "Sheetname2"
DoCmd.TransferSpreadsheet acImport, , "DataImport", FileName,
False, "Sheetname3"
DoCmd.TransferSpreadsheet acImport, , "DataImport", FileName,
False, "Sheetname4"

My Sheetname1 and Sheetname3 have only 4 fields, Sheetname2 and Sheetname4
have 12 fields.

When I run this code I get the following error:

Field 'F5' doesn't exist in destination table 'DataImport'. Is there any
other way of exporting an Excel workbook to a single table in one shot, if
not what can I do correct the issue.

Thank you!
 
When you're importing to an existing table, the data you're importing
obviously can't have more fields than there are in the table.

By the sound of it, the table "DataImport" doesn't exist when you start
the process, so is created automatically by the first
TransferSpreadsheet process. Try importing Sheetname2 first: that way
the table will be created with all 12 fields.
 
Back
Top