TransferSpreadsheet - Data Conversion Error

[JAB]

I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:

DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""

Everything works fine except the column which contains 'Material' holds both numeric and alpha-numeric values. For example these are both material numbers: 156952 and 1238707-202. The data in this column is formatted as General. The data type for Material in tblMPSDATA is Text, 18 character length. The alpha-numeric materials are all at the end of the file. When I import, an error table is created listing the alpha-numeric materials with the error 'Type Conversion Failure'. But if I have an alpha-numeric material in the first row of data then everything is imported just fine.

I have set up a nice little popup form with a file path and command button for controlling the process of bringing in this data. I really do not want to have to add special instructions about making sure the Excel data is sorted in a certain manner prior to importing. Any thoughts on why Access is not treating everything in this column as text?

Thanks,
JAB

 

[Jacob Mathai]

First time, create the tblMPSDATA table. Then change the column type of the field (with numbers & alpha fields) to 'text' in the tblMPSDATA table.
Keep this table as your structure table. Do not delete it.

Before you do the 'transferspreadsheet' command again, delete all the rows from the tblMPSDATA table ( DELETE * from tblMPSDATA ).
Now,
Run the Docmd.Transferspreadsheet command again using this table. Pls let me know if it worked.

 

[JAB]

Thanks Jacob, but I'm already there. tblMPSDATA is an existing data, and the first step in the import routine is to empty the table with the delete sql as you showed. The material field is already set to text.

 

[SkiGeek]

Sounds Like we think alike. I also set up a form to automate the import and am recieving the same problem. From what I can tell it is because the transferspreadsheet command uses the first few rows to determine the column type. In your case if the first few rows are all numbers in a specific column it will set the column type to number and then future rows will get a type conversion error.

Have you solved this?

Thanks

 

[SkiGeek]

Found a solution, well a good description of the problem
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

But does anyone have any other solutions?

 

[Libre]

I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:

DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""

JAB

I am finding that when I run this code, I get an error unless the spreadsheet is open. Is that normal? Is there a way to import an Excel file from a spreadsheet if it is closed - or must it be open?