How to check if workbook not corrupted

Posted on


I have asked this on the main 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?


There is Err object, that contains information about run-time errors:

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
    CanBeOpen = true
    CanBeOpen = false
end if

Leave a Reply

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