Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Managing overlapping error handlers

Posted on 2011-09-23
4
Medium Priority
?
239 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 2000 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 93

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 35

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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