Solved

Managing overlapping error handlers

Posted on 2011-09-23
4
233 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:gabrielPennyback
4 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36591286
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36591362
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
 
LVL 33

Expert Comment

by:Norie
ID: 36592252
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36713812
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question