Thread: VBA in Access to detect a corrupt Excel workbook on open

In article <ace86e18-ce34-411b-830c-
e14f49099d75@k6g2000prg.googlegroups.com>, Mike wrote:
> 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?

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?

In article <d6e132e6-5eb3-419f-a43a-
15d53851f847@l12g2000prg.googlegroups.com>, Mike wrote:
> 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

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

In article <64a5fe1f-515f-4c24-b430-
170675a94cf8@v34g2000prm.googlegroups.com>, Mike wrote:
> 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.

>
> 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

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.

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

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.


In article <4c0d486f-f8af-4d8b-809c-
764a773c3000@b9g2000pri.googlegroups.com>, Mike wrote:
> 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