Error Handling question by MDW
MDW
Thu Jul 17 10:34:20 CDT 2003
>-----Original Message-----
>Pardon my newbie question.
>
>I have a script that opens a workbook, runs VBA in that
workbook
>(which in turn opens a database and retrieves data from
it), then
>closes the workbook. How can I modify my script so that
it will detect
>any error that might normally display a dialog box in
Excel, then
>close everything? The error could be an Excel error, it
could be
>displayed in Excel because of something that happened
during the
>database query, etc. I just need a common sort of, 'if
anything
>happens that wasn't supposed to happen, close the
workbook, make sure
>the database closes, destroy objects, go back to normal.'
>
>tod
>.
>
Probably the easiest thing to do is to identify which
lines of code have the potential to cause errors -
typically anywhere you open,close,or modify a file. Then
go this route:
On Error Resume Next
' Your code here
' Your code here
On Error GoTo 0
If Err.Number <> 0 Then ' An error has occurred
MsgBox "An error has occured. The error was: " &
Err.Description
Err.Clear
' Destroy objects
Exit Sub
End If
In theory, you COULD put the "On Error Resume Next" as the
very first line, and the "On Error GoTo 0" at the end.
Buuuut, what that would mean is, no matter how many errors
occur, it will pound forward, and at the very end it will
generate that error, but all the code will have executed.
Even if the first error was on the second line.