Test for Corrupt Excel File

[tcb]

We do a data import into Access from an Excel file. The xls file is
always formatted the same way. The import function never changes. It
works almost always, but occasionally the import fails.

If the user copies the data from the spreadsheet into a new
spreadsheet, saves, and tries again, the import works.

The original spreadsheet must be corrupt in some way, but it appears
just fine. Is there a way in vba to test the file to see if it is
corrupt? Any other clues on this?

 

[CLR]

What happens if the user just "tries again" without saving the data to a new
file?

Vaya con Dios,
Chuck, CABGx3

 

[tcb]

What happens if the user just "tries again" without saving the data to a new
file?

Vaya con Dios,
Chuck, CABGx3

They get the same error message. It fails.

 

[CLR]

Off the top of my head, it "feels" like a formatting problem, whereas some of
the numbers you expect to be numbers are actually TEXT, or
viceversa.........perhaps a test of the COUNT vs the COUNTA results on your
critical cells might help........

Vaya con Dios,
Chuck, CABGx3

 

[tcb]

Does the cutting and pasting into a new spreadsheet reformat the data
somehow? Because it always works after that act.

What do you mean by a test of the COUNT vs the COUNTA?

 

[CLR]

It could be, that somehow the format is changed during the cut/paste.

If you have real numbers in A1:A10, then =COUNT(A1:A10) will return 10,
and =COUNTA(A1:A10) will also return 10. However if you replace one of
those numbers with TEXT, (or a TEXT string that looks like a number), then
=COUNT(A1:A10) will return 9, while =COUNTA(A1:A10) will still return
10.......this tells you that although there are 10 entries in A1:A10, one of
them is actually TEXT.

Only other thing I can think of, is if you are performing the import
function via a macro, perhaps you could incorporate the copy/paste function
upon error. I know I've had to rework import macros in the past to
accomodate various combinations of leading spaces, etc....especially on
"Excel files" created by a dump from the company's main computer.

Chip Pearson has a little add-in on his site called CellView, that will let
you look at each cell in one of the files that will not import, to see if
there are any hidden characters that may be interfering with the
import......perhaps that would help.

Sorry I don't have a quick and easy answer for you.

Vaya con Dios,
Chuck, CABGx3

 

[tcb]

Thanks for your answers. I'll follow up on your ideas. The
spreadsheet to be imported is created via a data dump from a company's
main computer, just as in your case.

The failure happens so infrequently that it's hard to spend a lot of
time on the problem. Last time it happened was four months ago.
Nonetheless I'd like to get it resolved!

 

[CLR]

FWIW, my troubles came from different Main Computer Operators doing things
different with the different dumps.....I don't know how they varied things,
but I clearly saw the results and had to deal with them accordingly. MIS
refused to standardize the procedure to give me the same thing every time, so
I had to accomodate the differences in my macros.

Vaya con Dios,
Chuck, CABGx3