Import from Excel to Access

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

Guest

I want to use code to import an Excel spreadsheet into an Access table. I
can currently import the file manually (clicking) into an existing table or
into a new table with no errors.

But when I use this code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSCR",
myFileName, Yes

I get this error:
Field 'F1' doesn't exist in destination table 'tblSCR'.

This doesn't make any sense. I am trying to import the same file into the
same table and it doesn't complain when I am doing all the clicking!

Can anyone help with this?
Thank you, Judy
 
Judy,

I suspect the problem is the value you provided to the HasFieldNames
argument: in VBA it should be True, not Yes. If my theory is correct,
then VBA doesn't recognise the Yes as a Boolean value, so it assumes the
default False. This tells Jet (the Access database engine) there is no
field name row at the top, and Jet assigns generic field names F1, F2
etc. Change Yes to True and see what happens.

HTH,
Nikos
 
Thank you very much for this response. I was able to import using True
instead of Yes.
 
Back
Top