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?
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 wb.close CanBeOpen = true else CanBeOpen = false end if