Problem importing excel spreadsheet into Access

[odin1701]

I'm getting this error:

"Field 'F1' doesn't exist in destination table 'tblImport'"

I am using this command:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", filename

filename is a variable containing the full path and filename of the file to be imported. This module imports all files in a certain directory.

If I use this command:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", filename, True

It will work, but I have to of course put the field names in the first row in excel. I want to just have raw data, no field names. Am I doing something wrong?

 

[Guus2005]

Yes, your input file shows a field called F1 which doesn't exist in your table.

 

[Jacob Mathai]

One way to handle the problem is to delete table 'tblimport' (if it exists) before you execute the 'transferspreadsheet' command.

It depends on how you want to handle it.

 

[Guus2005]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", filename, [bold]True[/bold]

That's what the last True does. It creates a new table.

 

[odin1701]

Yes, your input file shows a field called F1 which doesn't exist in your table.

No, it doesn't.

The problem is actually that when not using defined field names in the excel spreadsheet, Access uses default field names of F1, F2...Fx, instead of just putting my first column of data into the first column in the table, etc.

And no, the True at the end does not create a new table - it tells Access whether or not the Excel spreadsheet being transferred has column headers in the first row of data.

I solved this a few days ago, and in order for this to work I just have to put field names in the excel sheets.

 

[Guus2005]

No, it doesn't.

The problem is actually that when not using defined field names in the excel spreadsheet, Access uses default field names of F1, F2...Fx, instead of just putting my first column of data into the first column in the table, etc.

And no, the True at the end does not create a new table - it tells Access whether or not the Excel spreadsheet being transferred has column headers in the first row of data.

I solved this a few days ago, and in order for this to work I just have to put field names in the excel sheets.

Thank you for getting back to us and setting me straight.