Import from Excel to Access

[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

 

[Nikos Yannacopoulos]

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

 

[Guest]

Thank you very much for this response. I was able to import using True
instead of Yes.