Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Managing overlapping error handlers

Posted on 2011-09-23
4
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 34

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 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