Error handler not working

[Russj62]

I have code that among other things, copies an excel file from our server, renames it and places it into another file. Occasionally remote users get errors at this "FileCopy Source" line. My assumption is that there was a data transfer issue (or other issue) that caused the file to not be copied. So, I have been attempting to write error handling to 1. attempt the "FileCopy Source" one more time, 2. if that causes another error, notify the user of the error and to remove some information that was entered into a worksheet via previous code. On the "FileCopy Sourse" line, I have purposefully changed the file name to one that does not exist to force an error. I have been messing with the error handling all day and can not get it to work. I simply get the File Not Found error. In my latest attempt to simplify, I have removed one error handler and just hoping to get "Punt" to work for starters.

Any help would be greatly appreciated.

     'Add SCCO Workbook
        NewSCCO = SCLogPath & "\" & FixSubcontractor & Number & ".xls"
        'ATTEMPT TO CORRECT ERRORS THOUGHT TO BE CAUSED BY DATA CONNECTION
        On Error GoTo Punt
        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCOx.xls", Destination:=NewSCCO
        On Error GoTo 0
'
'
'
'many lines of working code
'
'
'
Exit Sub
'SCcoError:
'        'On Error GoTo Punt
'        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCO.xls", Destination:=NewSCCO
'        On Error GoTo 0
'    Resume Next
Punt:
    Worksheets("SC Log").Unprotect "2132"
    Range(ItemRow).Clear
    Worksheets("SC Log").Protect "2132"
    ActiveWorkbook.Save
    MsgBox "There was an error that prevented the creation of the Subcontract CO file.  Please try again.  If the problem persists, contact Admin"
    Resume Next
End Sub

 

[Hermanito]

At first glance I see no major issue... it should be working.

When you step through the code, does it actually step into the errorhandler or not? When exactly does the File Not Found error pop up?

Also, in the VBA editor, check in Tools/Options/General tab, section Error Trapping. It should not be 'Break on All Errors' there, if it is, change it to one of the other two (doesn't matter which for now), and try again...

 

[Russj62]

No, it never makes it to the errorhandler, but simply gives me the File Not Found error at the line: FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCOx.xls", Destination:=NewSCCO.

My error handling is set to "Break on unhandled errors."

 

[Hermanito]

Strange...

Could you try this simple testprocedure separately (but in the same Excelfile), to exclude all other influences, just to check if errorhandling works:

Public Sub test()
    On Error GoTo ErrTest
    Dim L As Long
    L = 50 / 0
    Exit Sub
ErrTest:
    Stop
End Sub

When you run it, it should break on the Stop line...

If it doesn't work in the same file, try this procedure in a new file. If that also doesn't work, close and reopen Excel, and try it again in a new file...

 

[shg]

I think an error handler can't handle its own errors, or change the error handler. See, for example, http://www.cpearson.com/excel/errorhandling.htm

You could handle it in-line:

    Dim iTry As Long
 
    On Error Resume Next
    Debug.Print 1 / 0
 
   Do While Err.Number
        Err.Clear
        iTry = iTry + 1
        If iTry > 3 Then GoTo Punt
        Debug.Print 1 / 0
    Loop
    On Error GoTo 0
 
    '...
 
    Exit Sub
 
Punt:
    ' code here

 

[Russj62]

Your test went to the error handler correctly.

 

[Hermanito]

Can you comment out the FileCopy line then, and instead put

Dim L As Long
L = 50 / 0

there... to see if it jumps to Punt then?

 

[Russj62]

Can you comment out the FileCopy line then, and instead put

Dim L As Long
L = 50 / 0

there... to see if it jumps to Punt then?

This did not call the errorhandler either, i simply got the typical 'division by 0 error.'

 

[Hermanito]

So in another sub errorhandling works fine, in this sub it doesn't...

Then it has to be something in the code before... the part not shown... can you show more code? The whole sub?

 

[Russj62]

I think an error handler can't handle its own errors, or change the error handler. See, for example, http://www.cpearson.com/excel/errorhandling.htm

You could handle it in-line:

    Dim iTry As Long
 
    On Error Resume Next
    Debug.Print 1 / 0
 
   Do While Err.Number
        Err.Clear
        iTry = iTry + 1
        If iTry > 3 Then GoTo Punt
        Debug.Print 1 / 0
    Loop
    On Error GoTo 0
 
    '...
 
    Exit Sub
 
Punt:
    ' code here

Thanks for the suggestion, but this also simply gave me a 'file not found' error.