Access VBA: Is it possible to reset error handling?
On error goto 0
give hand to visual basic for error treatment (in general message box)
On error goto label
will redirect your code to label:
On error resume next
will ignore the error and continue
Resume next
redirect the code to the next line after the error is raised
it means that combinations of instructions such as
On Error goto 0 ... On Error goto 0
do not make sense
And if you want to redirect an "on error" instruction you'll have to do it this way:
Do While Not rs.EOF On Error Resume Next rs2.Open strsql On error Goto 0 rs2.moveNext Loop
If you want to redirect an error to a label (for treatment or whatever) and then go back to the code where the error occured, you have to write something like:
On error goto label ... ... On error goto 0 exit sub (or function) label: .... resume next end function
But I really advise you to be more rigorous on your error management. You should first be able to do something like that:
Set objexcel = CreateObject("excel.Application") objexcel.Visible = True On Error GoTo error_Treatment wbExists = False Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls") Set objSht = wbexcel.Worksheets("Sheet1") objSht.Activate wbExists = True On error GoTo 0 Set db = DBEngine.opendatabase("C:\book.mdb") Set rs = db.OpenRecordset("records") Set rs2 = CreateObject("ADODB.Recordset") rs2.ActiveConnection = CurrentProject.Connection For Each tdf In CurrentDb.TableDefs .... 'there are a number of potential errors here in your code' 'you should make sure that rs2 is closed before reopening it with a new instruction' 'etc.' Next tdf Exit sub error_treatment: SELECT Case err.number Case **** '(the err.number raised when the file is not found)' objexcel.Workbooks.Add Set wbexcel = objexcel.ActiveWorkbook Set objSht = wbexcel.Worksheets("Sheet1") Resume next 'go back to the code' Case **** '(the recordset cannot be opened)' .... .... Resume next 'go back to the code' Case **** '(whatever other error to treat)' .... .... Resume next 'go back to the code' Case Else debug.print err.number, err.description '(check if .description is a property of the error object)' 'your error will be displayed in the immediate windows of VBA.' 'You can understand it and correct your code until it runs' End select End sub
The next step will be to anticipate the errors in your code so that the err object will not be raised. You can for example write a generic function like this one:
Public function fileExists (myFileName) as Boolean
You can then take advantage of this function in your code by testing the existence of your xls file:
if fileExists("C:\REPORT3.xls") Then Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls") Else objexcel.Workbooks.Add Set wbexcel = objexcel.ActiveWorkbook Endif Set objSht = wbexcel.Worksheets("Sheet1") objSht.Activate
You do not need your wbExist variable anymore...
In the same way, you should anticipate the case where your recordset has no records. Writing down rs.MoveFirst before testing it could raise an error. You should then write
If rs.EOF and rs.BOF then Else rs.moveFirst Do while not rs.EOF rs.moveNext Loop End If
You need to clear the error. Try putting this code in:
If Err.Number > 0 Then Err.ClearEnd If
You can also use Err.Number to handle specific error cases.
It is nearly always better to avoid errors, rather than handling them. For example:
Set objexcel = CreateObject("excel.Application")objexcel.Visible = True'On Error GoTo Openwb ''wbExists = False 'If Dir("C:\REPORT3.xls") = "" Then objexcel.Workbooks.Add Set wbexcel = objexcel.ActiveWorkbook Set objSht = wbexcel.Worksheets("Sheet1")Else Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls") Set objSht = wbexcel.Worksheets("Sheet1")End IfobjSht.Activate'wbExists = True '