TransferSpreadsheet excel to access data error

[Grit]

I have writen vb code to import an xls. file into a new table in access. The source xls. file is all formatted as text. It works with no data errors, so long as the excel file is open. However if the xls file is closed I get corrupt data in the table e.g.

excel to access

0200240 -> 0200240 ok
2800200 -> 2.8002e+006 error
Do Not Fit -> Do Not Fit ok

DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel97, "ColData", FormMain.TextPrjDir.Text + "Cols.xls", True

where - FormMain.TextPrjDir.Text= source directory

I've searched the forum and found one similar question regarding 'importing with decimal sign' 07-18-2005 but there is no solution.

any suggestions?

 

[namliam]

You are importing into a number field and the number field is 'to small' to handle the number.

Tho of the top of my head I dont know which number has 2.something million as a limit...

Try making the field a Long Integer or Double, this should relieve your problem

If you have an excel file open, I dont think you can import it....

 

[Grit]

Thanks that sounds good, but I'm don't how to specify the data format for the field, this isn't part of the transferspreadsheet function is it?
Would it be a tabledef ? if so I've not used them before so any tips would be appreciated

[it definitely works when the excel file is open.... I am running a vba exe file that is working on an access database as an object [Dim appExcel As Object]]

 

[namliam]

Yep that would then be part of the tabledefinition...

 

[modest]

When the spreadsheet is closed, after you transfer it, goto the table's design (right click the table and choose the design view) and look at the datatype of that field. If it is a number it is not transfering it as text.

Furthermore, post your code, because I would like to see what you mean by being.