How to check if workbook not corrupted

Question

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?

Vmesser · Accepted Answer · 2017-11-15 09:21:35Z

1

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

answered Nov 15, 2017 at 9:21

Vmesser

362 bronze badges

\$\begingroup\$ this is more elegant way than mine. Still, it try to open the file, which may be time consuming. \$\endgroup\$

MarcinSzaleniec
– MarcinSzaleniec

2017-11-15 11:30:06 +00:00
Commented Nov 15, 2017 at 11:30
\$\begingroup\$ Let’s think about it. File couldn’t be corrupted as it is. It is corrupted only from point of view of Excel, because he can’t open it. It means that to determine this corruption without using Excel we need to know excel-corruption-determine algorithm, but we don’t. Just my thoughts. \$\endgroup\$

Vmesser
– Vmesser

2017-11-15 19:46:21 +00:00
Commented Nov 15, 2017 at 19:46

Add a comment |

Stack Exchange Network

How to check if workbook not corrupted

1 Answer 1

You must log in to answer this question.

Hot Network Questions

How to check if workbook not corrupted

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions