Importing Spreadsheet Field Names Issue

[collekt]

Hey guys, I'm relatively new to working with access and I'm hoping someone can lend me a hand or tell me if this is even possible.



I have an Excel spreadsheet that is generated daily, and it contains only data so the first row is data with no titles or field names at the top. Is there any way to import the spreadsheet into an existing table that already has the field names laid out, to avoid them being assigned as F1, F2, F3, etc.? Right now I'm importing it with DoCmd.TransferSpreadsheet and I'd like it to just import the columns in order, using the pre-existing field names of the table that is already created. Is this possible? Thanks for any help or ideas, and I hope I made it clear enough.

[June7]

Maybe if you use a saved import specification. I've not tried import specifications so not sure how robust that would be.
http://office.microsoft.com/en-us/ac...010171498.aspx

There are other ways to import from Excel but the code will be more complicated.
http://forums.aspfree.com/microsoft-...el-414974.html

[collekt]

Thanks for the ideas. The saved import won't work because it greys out the checkbox and forces me to use the first row of the spreadsheet as field names. However, after searching forever I finally found a solution. In case anyone ever comes across this thread with the same issue, I'll post it below:

Basically, I import into a temporary table and allow it to set the field names as F1, F2, F3, etc. Then, I use the following sql statement to append the temp table into the main table. This sql statement inserts the data from the temp table into the main one in order of columns, using my preexisting field names. Test1, Test2, etc. are the field names I created in the main table, and F1, F2, etc. are the corresponding field names from the temp table.

INSERT INTO Table1 ( Test1, Test2, Test3, Test4, Test5, Test6, Test7, Test8, Test9, Test10, Test11, Test12, Test13 )
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13
FROM tmpTable AS ['b];