Solved: Problem importing excel spreadsheet into Access

[OdiN]

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?

[OBP]

It cuold be that the Column Heading in question has either an illegal name (as far as Access is concerned) or a name that has been used before.
I have a routine for browsing to an Excel Workbook and importing it that you can try if you can't get the standard version to work.

[OdiN]

Well this module actually imports 51 worksheets in one go, so I need that functionality, but it's just strange that if the field names are present, the import works, but if not it doesn't. The data is exactly the same.

I can do a copy/paste between excel and access with no problems.

I made sure that everything is in the correct order, and it is.

There is no key set so there should be no violations, just a straight import.

It works fine with my headings. With no headings is when it doesn't work and I get that error, which is what is puzzling me.

Even more puzzling is that it talks about this "F1" field. There is no such field and no data matching that anywhere in the spreadsheet or the access table.

[OBP]

It could be that you are trying to put the data in to the one Table "tblImport".
If you imported the data in to different tables do you still get the same eror?

[OdiN]

It could be that you are trying to put the data in to the one Table "tblImport".
If you imported the data in to different tables do you still get the same eror?

All the data goes into that table - there is no other table for importing. The tblImport table is a copy of the main data table's structure, minus the key and minus data fields which are used for user input, but do not exist in the raw excel data. It works fine in that table with the column headings, just not without.

[OBP]

That is the point, does the Sheet without Headings Import OK in to a NEW TABLE?
If it does then you can import in to a new table and use a Query to transfer the data to your main table.

[asingh]

Hi,

give this a try...to troubleshoot...

Go to the destination table....design..and after the last Field name create another field..and name it F1. Your import method will work..cause the sequence will now see a field name F1. See what the transfer spreadsheet..pushed into this field...after all 50 files are imported...!

After that..check your excel...strucuture. What I think will happen...F1 in the accesss table will not have anything...some how....the range you are supplying for transfer spreadsheet..has a field which as a naming violation..or his a blank. Are any columns hidden...but are including in your range...when you supply the transfer spreadsheet parameters...?

I have set up something similiar..but I supplied excel ranges..in the transfer spread sheet..method..something like:

DoCmd.Transferspreadsheet acImport, 8, storage_tbl_nm,full_file_path_with_nm, True, xls_range.

Where:
storage_tbl_nm: is the destination Access table.
full_file_path_with_nm: is the full path of the source file..including the name.
xls_range: is the excel range..starting with the column headers..till the last value of the cell with data.
Exampe: Sheet1!A1:C55.

This includes my column headers which are exactly the same as my field names set up in access.

regards,

asingh

[OdiN]

Thanks for the thought. I didn't think to check for hidden fields...but these spreadsheets are supposedly raw data right out of a query so it shouldn't have any special formatting. I will check.

[OdiN]

Okay I figured out what it's doing.

For each cell in excel, it tries to import into a field "F1, F2, F3....F24"

So instead of using the field names I have setup in the import table, it wants to use generic names, which I don't want to do so I guess I have to have the Excel files formatted with field names. I thought that access would just import the first cell to the first field, second to the second field, etc.

[OBP]

OdiN, that was why I suggested using the Temporary Table to import the data and then append the data to the Real table using a Query or VBA Recordset.

[OdiN]

OdiN, that was why I suggested using the Temporary Table to import the data and then append the data to the Real table using a Query or VBA Recordset.

I'm using SQL to transfer all data between tables. So using headers in the Excel file just seems to be the easiest way. I can do up a macro which will format all of the files in the import directory a certain way.