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