VBA in Access to detect a corrupt Excel workbook on open

[mike]

I am running some code within Access 2003 that opens and manipulates
then saves an Excel 2003 workbook.

The problem that I have is that once in a while the workbook that I
get from our vendor (through FTP) is corrupt.
"The fils is not in a recognizable format"

How can I test for this before proceeding into my code, and exit
gracefully with a msgbox if it is corrupt?

I am using an Access form with a button to start the process.

 

[Steve Rindsberg]

I am running some code within Access 2003 that opens and manipulates
then saves an Excel 2003 workbook.

The problem that I have is that once in a while the workbook that I
get from our vendor (through FTP) is corrupt.
"The fils is not in a recognizable format"

Are you ensuring that the FTP download is set to Binary rather than
ASCII/Text or Automatic?

 

[mike]

Yes, it's binary.
I get it just maybe 1 out of 100 times.
I think it gets corrupted as the vendor creates it.

Anyway, it exists, so I have to test for it.

Any idea on the code?

 

[Steve Rindsberg]

Yes, it's binary.
I get it just maybe 1 out of 100 times.
I think it gets corrupted as the vendor creates it.

Anyway, it exists, so I have to test for it.

Any idea on the code?

How about setting Application.DisplayAlerts to False (for Excel) so
it's not issuing unwanted yelps, then after you try to acquire a
reference to the workbook, test for objWorkbook Is Nothing

Dim objWorkbook As Object
On Error Resume Next
Set objWorkbook = GetObject("whatever")
If objWorkbook Is Nothing Then
MsgBox "Woops"
End If

 

[mike]

No Go.
This code on a good and corrupt XLS workbook produces rthe same
result.

Private Sub Command1_Click()
Dim objWorkbook As Object
On Error Resume Next
Set objWorkbook = GetObject("C:\San CORRUPT.xls",
"Excel.Application")
If objWorkbook Is Nothing Then
MsgBox "CORRUPT"
Else
MsgBox "GOOD"
End If
End Sub

 

[Steve Rindsberg]

No Go.
This code on a good and corrupt XLS workbook produces rthe same
result.

That's why I suggested turning the Excel Application.DisplayAlerts off
... you don't want IT handling errors.

 

[mike]

Actually, it doesn't generate an error other than the alert.
If you turn off the alert then it just opens up the garbled corrupted
data workbook.

I need to find out and trap the alert.

Anyone with an idea?

I can send a coprrupted workbook, if anyone wants to give it a try.

 

[mike]

Here's the current code I'm using attached to a button in Access:

Private Sub command8_click()

Dim appExcel As Excel.Application

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0

On Error GoTo WarnMe

appExcel.Application.DisplayAlerts = False
appExcel.Workbooks.Open ("C:\san corrupt.xls")
appExcel.Application.Visible = True


WarnMe:
MsgBox ("error number" & Err.Number)

'appExcel.Quit
'Set appExcel = Nothing

End Sub

 

[Steve Rindsberg]

Thanks for posting that, Mike.

Interesting results ...

I modified it slightly:

Private Sub Test()

Dim appExcel As Excel.Application

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
End If

' no need for this:
'On Error GoTo 0

On Error GoTo WarnMe

appExcel.Application.DisplayAlerts = False
appExcel.Workbooks.Open ("C:\temp\bogus.xls")
appExcel.Application.Visible = True

' Added this here ... else you get the warning msgbox no matter what
Exit Sub

WarnMe:
MsgBox ("error number" & Err.Number)

'appExcel.Quit
'Set appExcel = Nothing

End Sub


I saved a file from Excel as bogus.xls then opened it in notepad and
added "CORRUPT" to the very beginning of the file.

When I ran the code, there were no error messages; when I checked on
Excel, it had happily opened the file as a text file and was displaying
all sorts of gibberish.

I edited the XLS in notepad again, removed the "CORRUPT" that I'd added
before, scrolled down a bit, selected a bunch of compugibber and
deleted it.

This time when I ran the code it behaved as you'd want it to ... error
message and all.

So the problem is that if the file itself tells Excel that it's indeed
an XLS, Excel will fail to open it and you get your trappable error
condition. If the file's corrupted in such a way that it doesn't
internally CLAIM to be an Excel file, Excel treats it as text and opens
it any which way, gives you no error msg.

I'd bet that the first few characters in the file are signature bytes
that ID the file as an XLS. You might want to read those from a
prospective file and if they aren't what they should be, refuse to open
the file in Excel. If the sig bytes ARE correct, turn it over to Excel
and let it scream about further inconsistencies, which you'll be able
to trap for.

Are we having fun yet? Please tell me we're having fun.

 

[mike]

So what triggers the warning and isn't that trappable?


I saw some things that might help / apply in the object browser but
with no details...

XLcorruptLoad which has: xlExtractData, xlNormalLoad, xlRepairFile
and
XLdvAlertStyle which has: xlValidAlertInformation, xlValidAlertStop,
xlValidAlertWarning


Also, the "corrupt" window that pops up with the three choices has a
title of just "Microsoft Excel" maybe that could be trapped, as the
Excel session title would read "Microsoft Excel - san.xls"

 

[Steve Rindsberg]

So what triggers the warning and isn't that trappable?

I'm curious .. did you try the code sample I posted?
In the tests I made, it prevented the warning from Excel and trapped
the resulting error.

 

[mike]

Yes, mine just opens as garbled text, no error thrown

 

[mike]

Just like your experience here...

"I saved a file from Excel as bogus.xls then opened it in notepad and
added "CORRUPT" to the very beginning of the file.

When I ran the code, there were no error messages; when I checked on
Excel, it had happily opened the file as a text file and was
displaying
all sorts of gibberish."

 

[Steve Rindsberg]

Yes, mine just opens as garbled text, no error thrown

So did you look at the first couple of bytes in your problem file?

Do they match what you find in a known-good Excel file?

 

[Steve Rindsberg]

Just like your experience here...

"I saved a file from Excel as bogus.xls then opened it in notepad and
added "CORRUPT" to the very beginning of the file.

When I ran the code, there were no error messages; when I checked on
Excel, it had happily opened the file as a text file and was
displaying
all sorts of gibberish."

Read the rest of my post ... I went on to address why this happens.

 

[mike]

I understand why, but I'm looking for a soultion that addresses the
warning not the first bits of the excel file.

Does one exist?
How about the XLcorruptLoad or XLdvAlertStylertStyle?

 

[Steve Rindsberg]

I understand why, but I'm looking for a soultion that addresses the
warning not the first bits of the excel file.

Does one exist?

If I knew of one, I'd have said so.

But it appears to be the first bits of the file (ie, whether they're an
Excel signature byte sequence or not) that determines whether Excel
will try to open the file AS an Excel file (and give you a trappable
error) or as some type of Text (giving you gibberish in Excel, no
warning from it).

Seems like a simple enough way to solve the problem.

How about the XLcorruptLoad or XLdvAlertStylertStyle?

No idea.