vba Error handler for Excel - make it close on error

[scolivas]

Hi, I asked yesterday about this - but maybe my title was bad. No one responded.

I have a refreshable Excel Spreadsheet that pulls data from an Access Database on the server. The server has been a pain lately - and not letting anything finish and causing lots of time outs.

I want to put an error handler into my vba macro that will force the application to quit if any errors occur.

I am thinking it is simply this:

sub name

on error goto Myerrorhandler

'rest of macro

Myerrorhandler:
application.quit

end sub


Is this right? I work remotely so I am not able to kill a job that gets hung - so i want to make sure my code is right before i do it.

Thanks for your help!

Sophie

[shrimant]

it should be:

Application.DisplayAlerts = False
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
 

[scolivas]

it should be:

Application.DisplayAlerts = False
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
 

so i put your code in under Myerrorhandler? instead of my code?

[kadghar]

so i put your code in under Myerrorhandler? instead of my code?

application.quit
in a vba code will close your excel, acces or the office application you're working with.

application.displayalerts = false
will make the alerts like "do you want to save changes?" no to show

so when you write activeworkbook.close it will close the active book you have in excel without asking.

I dont know if what you mean with "application" is just the macro or excel or acces. If its the macro, an "exit sub" will do. If you have a UserForm, you'll have to quit it before exiting the sub.

HTH

[kadghar]

i forgot to tell you.

If you want the easy way (case u're running a macro)

End

Will do

[scolivas]

i forgot to tell you.

If you want the easy way (case u're running a macro)

End

Will do

Hi, thanks for the info. I do infact want the whole application to close. We have these reports sitting on a desktop and on a scheduler - so they run every hour. When they are finished refreshing - they close so the next one can run. Problem we are having is if one gets stuck due to an ODBC error - the application won't quit - and the next inline doesn't get to run. (or at least that is how it seems to be working) The other ones try to open - but because of the ODBC Error the macro's don't appear to run...so they all just sit there in limbo.

My actual code is as follows:

Sheets("R30 Hours Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R30 Hours Summary").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("R30 Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R30 Compliance").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("R31 Hours Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R31 Hours Summary").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("R31 Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R31 Compliance").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("R32 Hours Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R32 Hours Summary").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("R32 Detail").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("R32 Compliance").Select
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveWorkbook.Save
    Application.Quit
 
    End Sub
 

That was built by someone else.

I think that we should be able to use something simpler like this:

Private Sub Workbook_Open()
 
     On Error GoTo ErrorHandler
 
          ActiveWorkbook.RefreshAll
 
          ActiveWorkbook.Save
 
          Application.Quit
 
     ErrorHandler:
            Application.Quit
 
End Sub
 
 

instead of the way it is - but I could be wrong. (so far the 2 reports I set up like this w/out the error handler part - don't always run, but I am not entirely convinced that it is the report - i am thinking it has something to do with the server timing out - and the original way - does make it so you refresh one data source at a time - but there is still no error handling - so it crashes too)

I tried adding a resume function in for the error handler...and that was a nighmare I don't wish to repeat.

Anyway...if you have any suggestions...I'd really appreciate it.

Thanks!
Sophie

[kadghar]

I think that we should be able to use something simpler like this:
...
instead of the way it is - but I could be wrong. (so far the 2 reports I set up like this w/out the error handler part - don't always run, but I am not entirely convinced that it is the report - i am thinking it has something to do with the server timing out - and the original way - does make it so you refresh one data source at a time - but there is still no error handling - so it crashes too)

I tried adding a resume function in for the error handler...and that was a nighmare I don't wish to repeat.

Anyway...if you have any suggestions...I'd really appreciate it.

Thanks!
Sophie

The first code you posted was made with the macro recorder, so there's always a way to make it simpler. The error handler is a great idea. But if you want to have more control of the errors, i'd recomend you to use

On Error Resume Next

(this will just ignore the code lines with an error)

and at the end of the code, put something like

If Err.Num = [The number of the error you want to detect] then
exit sub 'Or whatever you want to do
end if

you can make a SELECT CASE Err.Num

Now, the problem here is that Err.Num is the number of the last error, so if you had many errors during the execution of the code, you wont be able to see the first ones.

[vijayendradj]

The first code you posted was made with the macro recorder, so there's always a way to make it simpler. The error handler is a great idea. But if you want to have more control of the errors, i'd recomend you to use

On Error Resume Next

(this will just ignore the code lines with an error)

and at the end of the code, put something like

If Err.Num = [The number of the error you want to detect] then
exit sub 'Or whatever you want to do
end if

you can make a SELECT CASE Err.Num

Now, the problem here is that Err.Num is the number of the last error, so if you had many errors during the execution of the code, you wont be able to see the first ones.

Thnks a lot... It helped me in fixing my problem...