Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Managing overlapping error handlers

Posted on 2011-09-23
4
Medium Priority
?
241 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 36

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

578 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