Managing overlapping error handlers

I have some overlapping error handlers and I can't figure out how to make the second one work. The first error handler deals with which of two naming conventions a given file might have. But then I need something that goes to the next cel in [Customers] if neither of the naming conventions are present for the current cel.

How would I write that? Humor me along with my GoTo names. Just a little comic relief in a long, glorious day of coding.

Thanks,
John
Sub OpenBooks()
Dim cel As Range
For Each cel In ActiveSheet.[Customers]
    Dim wb As String, wb2 As String
    wb = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & ".xls"
    wb2 = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & "_2BCoded.xls"        '& [XLVersion]
On Error GoTo Paris
ChDir ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\Coded\"
    Workbooks.Open Filename:=wb
    GoTo Rome
Paris:
        Err.Clear
ChDir ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\2BCoded\"
Application.DisplayAlerts = False
On Error GoTo Madrid
    Workbooks.Open Filename:=wb2
Rome:
Call AddNewWeek
Madrid:
        Err.Clear
Next cel
Application.DisplayAlerts = True
End Sub

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
I rewrote it this way, and could be your patch, I "cleaned up" the error traps (at least to the way I code)
Sub OpenBooks()
Dim cel As Range
Dim wb As String, wb2 As String

    Application.DisplayAlerts = False
    
    For Each cel In ActiveSheet.[Customers]
        
        wb = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & ".xls"
        wb2 = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & "_2BCoded.xls"        '& [XLVersion]
        
        On Error GoTo Paris 'if first file doesn't open
        ChDir ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\Coded\"
        Workbooks.Open Filename:=wb
        On Error GoTo 0 'reset On Error when what you're looking to trap is no longer valid
        GoTo Rome
        
Paris:
    
        On Error GoTo Madrid 'if second file doesn't open
        ChDir ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\2BCoded\"
        Workbooks.Open Filename:=wb2
        On Error GoTo 0 'reset On Error when what you're looking to trap is no longer valid
        
Rome:
    
        Call AddNewWeek
        
Madrid:
        On Error GoTo 0 'reset On Error when what you're looking to trap is no longer valid
    Next cel

    Application.DisplayAlerts = True
    
End Sub

Open in new window


As an alternative, I would have written it this way.  Note the error trapping is tight and no go-to's required:
 
Sub OpenBooks()
Dim cel As Range
Dim wb As String, wb2 As String
Dim bSuccessfulOpen As Boolean

    Application.DisplayAlerts = False
    
    
    For Each cel In ActiveSheet.[Customers]
        
        wb = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & ".xls"
        wb2 = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & "_2BCoded.xls"        '& [XLVersion]
        
        bSuccessfulOpen = getFile(ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\Coded\" & wb)
        
        If Not bSuccessfulOpen Then bSuccessfulOpen = getFile(ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\2BCoded\" & wb2)
            
        If bSuccessfulOpen Then Call AddNewWeek

    Next cel

    Application.DisplayAlerts = True
    
End Sub
Function getFile(fName As String) As Boolean

    On Error Resume Next
    
    Workbooks.Open Filename:=fName
    
    If Err.Number = 0 Then getFile = True
    
    On Error GoTo 0
End Function

Open in new window


Dave
0
 
Patrick MatthewsCommented:
Personally, my favorite is:

On Error GoTo Hell

Open in new window


I've never worked up the nerve to include that in production code, though :)
0
 
NorieVBA ExpertCommented:
Perhaps you could use Dir to check if the file(s) exist.

Something like this perhaps, sorry if I go some of the logic wrong:
Sub OpenBooks()
Dim wbOpen As Workbook
Dim cel As Range
Dim wb As String, wb2 As String
Dim strPath As String

    strPath = ThisWorkbook.Path & "\..\" & cel & "\3_Working Files\"

    For Each cel In ActiveSheet.[Customers]

        wb = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & ".xls"
        wb2 = cel & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & "_2BCoded.xls"        '& [XLVersion]

        If Dir(strPath & "\Coded\" & wb) <> "" Then
            Set wbOpen = Workbooks.Open(strPath & "\Coded\" & wb)
        End If

        If wbOpen Is Nothing Then
            If Dir(strPath & "\2BCoded\" & wb2) <> "" Then
                Set wbOpen = Workbooks.Open(strPath & "\2BCoded\" & wb2)
            End If
        End If

        If Not wbOpen Is Nothing Then
            Call AddNewWeek
        End If

    Next cel

End Sub

Open in new window

0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Great suggestions, Dave, thanks. Inmorie, I don't think your code will help me in this particular situation, but thank you for posting.

Patrick, there have been many days when that's exactly what I've wanted to say :-)

Thanks,
John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.