why "Type Conversion Failure" import EXCEL into ACESS TEXT field

[Joe]

MS Access 2007
I am using a macro to TransferSpreadsheet into field in table.
The field is defined as TEXT 255 characters
The EXCEL file has 100 lines of data the first 50 have a null value in the
cell.
The remaining celss contain a TOS.
When import the last fifty lines receive a Type Conversion Failure.
If I place these fifty lines in the file first there is no error.
I can not control the way the file is filed out only its format. I have
locked the cells and fixed the format as text.
How can I stop the import error when the first few lines do not have a text
value?

 

[Jerry Whittle]

Are you importing into an existing table or creating a new table each time?
Access makes guesses based on the first few rows of an Excel spreadsheet. If
it sees nothing but numbers in the first dozen or so rows, it assumes that
the new field needs to be a number field.

The two ways around it are to create the table in advance with the proper
data types in the fields, OR to create a bogus first row in the Excel
spreadsheet having the correct data type example - then deleting the bogus
record once imported.

Seems that you have tried the first option as you mentioned that the field
is defined as Text 255 but it isn't working. Strange. Seems that the second
option worked when you shuffled the rows around.

What is a TOS? I'm not familiar with that acronym. Maybe that holds the key.