TransferSpreadsheet error

[RONDEAU Stephane]

Hello,

I try to import data from an excel spread sheet to an existing access table.
I'm using the TransferSpreadsheet function.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "LOAD_TABLE",
myFile.xls, False, "A:CQ"

but sometime i get an error message about a key violation.
After a lot of tests, i have found the reason of the message.
in fact a cell whose seems to be be clear are not clear.
I think there is a special caratere in the cell, because if i clear this
cell the file can be load in the table.

If you heard something about a similar case can you explain me how find
special caractere.

Thank you in advance
Stephane

 

[John Nurick]

Hi Stephane,

One thing you can do is to display the formulas in Excel (in English
versions, Tools|Options, View).

Also, the Excel worksheet functions ISBLANK() and LEN() can be used to
detect cells that contain information although they look empty.

Another approach is to import link the Excel sheet as a temporary table
and use an append query to correct the data as you move it to the
existing table.

 

[Stephane]

Thank you John.

There is a caractere in the cells, the function isblank() return false and
the function len() return 0.
So the cell look empty but in fact it is not !

Stephane