Clean up VBA error

R

RandyD123

Active Member
Joined
Dec 4, 2013
Messages
289
Office Version
  1. 2016
Platform
  1. Windows
All,

I have the following code below. As you can see that code makes 3 attempts to find my file. I'd like it to just find the file without an error on each attempt. If its in the first attempt, there is no error. If it's in the 2nd or 3rd attempt, then I get errors until it's found. Thank you for any help.

VBA Code: 
Sub OpenDOR()

    Dim wbMyWorkbook As Workbook
    Dim strWBName As String
    Dim strWBPathStub As String
    Dim strWBPath As String

    strWBName = "DO Report " & Format(DateAdd("d", -1, Date), "mm-dd-yy") & ".xlsm"   'yesterday's DO report
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"

    'Attempt 1
   strWBPath = strWBPathStub
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub

    'Attempt 2
   strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date), "yyyy.mm")
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub

   'Attempt 3
   strWBPath = strWBPathStub & "/Archived/"
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub

   MsgBox "Failed to locate " & strWBName

End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hello. Application.DisplayAlerts should do the trick

VBA Code: 
Sub OpenDOR()

    Dim wbMyWorkbook As Workbook
    Dim strWBName As String
    Dim strWBPathStub As String
    Dim strWBPath As String
    
    Application.DisplayAlerts = False
    
    strWBName = "DO Report " & Format(DateAdd("d", -1, Date), "mm-dd-yy") & ".xlsm"   'yesterday's DO report
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"
    
    'Attempt 1
    strWBPath = strWBPathStub
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
    
    'Attempt 2
    strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date), "yyyy.mm")
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
    
    'Attempt 3
    strWBPath = strWBPathStub & "/Archived/"
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
    
    MsgBox "Failed to locate " & strWBName
    
    Application.DisplayAlerts = True
    
End Sub
 
Upvote 0
Hi,
Untested but see if this update to your code does what you want

VBA Code: 
Sub OpenDOR()
   
    Dim wbMyWorkbook    As Workbook
    Dim strWBName       As String, strWBPathStub  As String
    Dim strWBPath       As String, FullName As String
    Dim i               As Long
   
    strWBName = "DO Report " & Format(DateAdd("d", -1, Date), "mm-dd-yy") & ".xlsm"        'yesterday's DO report
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"
   
    Application.DisplayAlerts = False
    On Error Resume Next
    For i = 1 To 3
        strWBPath = Choose(i, strWBPathStub, _
                              strWBPathStub & "/" & Format(DateAdd("d", -1, Date), "yyyy.mm"), _
                              strWBPathStub & "/Archived/")
        FullName = strWBPath & " \" & strWBName
        Set wbMyWorkbook = Workbooks.Open(FullName, 0, False)
        If Not wbMyWorkbook Is Nothing Then GoTo exitsub
    Next i
   
    MsgBox "Failed To locate " & strWBName, 48, "Not Found"
   
exitsub:
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

Dave
 
Upvote 0
Well the errors go away, but it can't find my file now!!! The old code still does.
 
Last edited:
Upvote 0
Sparcot said:
Hello. Application.DisplayAlerts should do the trick

VBA Code: 
Sub OpenDOR()

    Dim wbMyWorkbook As Workbook
    Dim strWBName As String
    Dim strWBPathStub As String
    Dim strWBPath As String
   
    Application.DisplayAlerts = False
   
    strWBName = "DO Report " & Format(DateAdd("d", -1, Date), "mm-dd-yy") & ".xlsm"   'yesterday's DO report
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"
   
    'Attempt 1
    strWBPath = strWBPathStub
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
   
    'Attempt 2
    strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date), "yyyy.mm")
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
   
    'Attempt 3
    strWBPath = strWBPathStub & "/Archived/"
    On Error Resume Next
    Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
    If Not wbMyWorkbook Is Nothing Then Exit Sub
   
    MsgBox "Failed to locate " & strWBName
   
    Application.DisplayAlerts = True
   
End Sub
Click to expand...
dmt32 said:
Hi,
Untested but see if this update to your code does what you want

VBA Code: 
Sub OpenDOR()
  
    Dim wbMyWorkbook    As Workbook
    Dim strWBName       As String, strWBPathStub  As String
    Dim strWBPath       As String, FullName As String
    Dim i               As Long
  
    strWBName = "DO Report " & Format(DateAdd("d", -1, Date), "mm-dd-yy") & ".xlsm"        'yesterday's DO report
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"
  
    Application.DisplayAlerts = False
    On Error Resume Next
    For i = 1 To 3
        strWBPath = Choose(i, strWBPathStub, _
                              strWBPathStub & "/" & Format(DateAdd("d", -1, Date), "yyyy.mm"), _
                              strWBPathStub & "/Archived/")
        FullName = strWBPath & " \" & strWBName
        Set wbMyWorkbook = Workbooks.Open(FullName, 0, False)
        If Not wbMyWorkbook Is Nothing Then GoTo exitsub
    Next i
  
    MsgBox "Failed To locate " & strWBName, 48, "Not Found"
  
exitsub:
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

Dave
Click to expand...
It can't find my file at all now!! The old code still works.
 
Upvote 0
You seem to have mixed path separators in there. Try something like:

VBA Code: 
Sub OpenDOR()
    Dim searchDate As Date
    searchDate = Date - 1
    
    Application.DisplayAlerts = False
    
    Dim strWBPathStub As String
    strWBPathStub = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"
    
    Dim strWBName As String
    strWBName = "DO Report " & Format$(searchDate, "mm-dd-yy") & ".xlsm"   'yesterday's DO report
    
    Dim searchFolders
    searchFolders = Array("", "/" & Format$(searchDate, "yyyy.mm"), "/Archived")
    
    Dim i As Long
    For i = LBound(searchFolders) To UBound(searchFolders)
      Dim strWBPath As String
      strWBPath = strWBPathStub & searchFolders(i)
      On Error Resume Next
      Dim wbMyWorkbook As Workbook
      Set wbMyWorkbook = Workbooks.Open(strWBPath & "/" & strWBName)
      If Not wbMyWorkbook Is Nothing Then Exit Sub
    
    Next i
    
    MsgBox "Failed to locate " & strWBName
    
    Application.DisplayAlerts = True
    
End Sub
 
Upvote 0
RandyD123 said:
Well the errors go away, but it can't find my file now!!! The old code still does.
Click to expand...

maybe because I seem to have added a space in the path separator

VBA Code: 
FullName = strWBPath & " \" & strWBName

should be

VBA Code: 
FullName = strWBPath & "\" & strWBName

but no worries if have code that resolved your issue

Dave
 
Upvote 0
Solution
dmt32 said:
maybe because I seem to have added a space in the path separator

VBA Code: 
FullName = strWBPath & " \" & strWBName

should be

VBA Code: 
FullName = strWBPath & "\" & strWBName

but no worries if have code that resolved your issue

Dave
Click to expand...
Yep, that did the trick. Thank You!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Copy and Paste from one workbook to another...
Replies
6
Views
272
RandyD123
R
R
  • Question
Search Subdirectories to locate and open file
Replies
0
Views
245
RandyD123
R
A
  • Question
Macro for extracting selected text and comment from Word to Excel on Mac
Replies
4
Views
142
aminchicago
A
duteberta
  • Question
VBA code to auto sort table based on any change within a specific column
2
Replies
17
Views
430
Peter_SSs
Peter_SSs
Pinaceous
  • Question
VBA how to prevent workbook from creating "Book1 - Excel"
Replies
4
Views
502
rabsofty
R

Forum statistics

Threads
1,215,247
Messages
6,123,857
Members
449,129
Latest member
krishnamadison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top