Error Handling

[Justinlabenne]

I need to provide some error handling for a file I developed at work. This file opens multiple workbooks with varying names, the workbooks are hours updated by 11 seperate people. Trouble is they keep renaming or moving their files every now and again. I had a basic "On error goto" but the user of my file would like to know which file was moved so he can pinpoint who dunnit. I cannot seem to get multiple error handlers to work correctly, and it seems like the long way round also. Heres is some code sampling below. Any ideas are welcome and more than grateful. Thanks

On Error GoTo Err_Mold
    M1 = "K:\AIC Public\EXPERT OJT\Blow Mold OJT\MOLDIN OJT MATRIX'S\Training Hours (All Shifts).xls"
    Workbooks.Open Filename:=M1
Range("One").Copy
       ActiveWorkbook.Close
Sheets("Mold").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
           ActiveSheet.Paste
PostOp1: 
    On Error GoTo Err_PostOp
    P1 = "K:\AIC Public\EXPERT OJT\Post Op OJT\1st Shift\HERB' MATRIX 1ST SHIFT.xls"   
    Workbooks.Open Filename:=P1
Range("Me").Copy
        ActiveWorkbook.Close
Sheets("PostOp").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
          ActiveSheet.Paste

Error handlers look like this:

Err_Mold:
    MsgBox "The filename below has been changed, moved, etc.. the information cannot be found " & vbCr & vbCr & M1
        Err.Clear
        GoTo PostOp1:

I am sure there is a better way I just cant seem to get to it.

[Jacob Hilderbrand]

Try something like this:
[vba]
Dim ErrorMessage As String
Dim ErrNum As Long

On Error Resume Next
Workbooks.Open Filename:=M1
ErrNum = Err.Number
On Error GoTo 0

If ErrNum <> 0 Then
ErrorMessage = ErrorMessage & vbNewLine & M1
Else
'The rest of your code for this file here
End If

On Error Resume Next
Workbooks.Open Filename:=P1
ErrNum = Err.Number
On Error GoTo 0

If ErrNum <> 0 Then
ErrorMessage = ErrorMessage & vbNewLine & P1
Else
'The rest of your code for this file here
End If


'More Code Here

If Len(ErrorMessage) > 0 Then
'There were some errors
MsgBox "The following files could not be accessed:" & vbNewLine & _
ErrorMessage, vbInformation, "Error Message"
End If
[/vba]

[Justinlabenne]

Jacob, that works perfect. You are once a again there to provide quick fixes to all my problems. Truly awesome. Thanks again, for all your responses. And does that mean I can get a job in programming someday? (Your avatar)

[Jacob Hilderbrand]

Glad to help.

Take Care