TransferSpreadsheet error

  • Thread starter Thread starter RONDEAU Stephane
  • Start date Start date
R

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
 
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.
 
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
 
Back
Top