Change column names to first row's data?

[Dugantrain]

Hi all. I'm trying to do a pretty quirky task, but it should get the results that I need:
-I'm importing data daily from an Excel worksheet. The worksheet is not constant; Project Managers will be adding columns, deleting, etc. When I use TransferSpreadsheet, Access's guesses as to the columns' data types are all wrong and it ends up rejecting data. So, if possible, I'd like to Transfer this Spreadsheet into a new Temp Table, specify "Has Column Names" as false so that Access will pull everything in as text (the Spreadsheet Headings are always text), and then, after the data is safely in Access, change the column headings from F1, F2...Fn to the column headings of the first row (and then, of course, delete that first row). I know that this is an odd way of doing things, but everything else that I've tried has been a dead end because of Access's poor data type decisions.

 

[cpod]

Access makes a guess as to data type by looking at the first 5 or 10 records. What I would do is add another now to the spreadsheet, just under the headings, and put an "x" in each of the used columns. Then, when you import into Access, all the fields will be text and your field names will be intact. All you would have to do is delete the "x" record.

 

[llkhoutx]

Maybe this will help you: The first data row determines the imported data's data type. All data in a column must be of the same data type or you have problems.

One solution would be to modify the Excel sheet via Access before importing. They delete your modification after importing.

The solution you propose would also work.

 

[Dugantrain]

Yes, I've considered my "X" option. However, as the users will be importing daily, I'd like for this to be an automated task. Another idea I had was to have the db copy this worksheet to another worksheet, add a row between the column headers and the data, add an "x" in every field, import that new spreadsheet, delete the spreadsheet, and then delete the "x" row that was imported. I would think this is possible, but I have no idea how to accomplish this task.

 

[Dugantrain]

Got It!

-TransferSpreadsheet Import from Excel, no column headings
-TransfterText OutputDelimited to .txt file
-TransferText ImportDelimited from .txt file, Column Headings True
-Kill .txt file, Drop unneeded tables

 

[jguscs]

I'm having the same problem you did, Dugantrain.
I tried your solution (importspreadsheet, transfertext, transfertext), but that did not work.
The part that didn't work for me was when Access tries to import the .TXT with column names (=TRUE), but Access just ignores that and uses F1..F23 for the column names.

Can anyone help?

here's the code I'm using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, File, FilePath, False
DoCmd.TransferText acExportDelim, , File, "C:\temp.txt", False
DoCmd.TransferText acImportDelim, , File, "C:\temp.txt", True
Kill ("C:\temp.txt")