PDA

View Full Version : [SOLVED] Error Handling



Justinlabenne
01-07-2005, 11:12 PM
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
01-07-2005, 11:37 PM
Try something like this:

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

Justinlabenne
01-08-2005, 01:46 AM
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
01-08-2005, 01:55 AM
Glad to help.

Take Care