How to check if workbook not corrupted

Posted on

Problem

I have asked this on the main stackoverflow.com site, but I have been redirected here. My problem is, how to check, if a workbook that is downloaded from sharepoint is not corrupted and can be open. Well, the simplest possible way is to try to open it. I have written a function for that:

Function CanBeOpen(sPath As String) As Boolean
Dim wb As Workbook
On Error Resume Next

With Application
.EnableEvents = False
.DisplayAlerts = False

Set wb = .Workbooks.Open(sPath, False, True)
.SendKeys "~", True 'To close progressbar when opening

Select Case TypeName(wb)
    Case Is = "Nothing"
        CanBeOpen = False
    Case Is = "Workbook"
        CanBeOpen = True
    Case Else
        CanBeOpen = False
End Select

wb.Close savechanges:=False
.EnableEvents = True
.DisplayAlerts = True
End With

Set wb = Nothing
On Error GoTo 0
End Function 

It works but I have strong feeling that this could be done in more efficient way. Do you have any suggestions?

Solution

There is Err object, that contains information about run-time errors:
https://msdn.microsoft.com/en-us/library/ka13cy19(v=vs.90).aspx

Err.Number represens error code, if no error Err.Number = 0

You can use this approach in your function:

On Error Resume Next
Set wb = .Workbooks.Open(sPath, False, True)
If Err.Number = 0 then
    wb.close
    CanBeOpen = true
else
    CanBeOpen = false
end if

Leave a Reply

Your email address will not be published. Required fields are marked *