Clean up VBA error

R

RandyD123

Active Member
Joined
Dec 4, 2013
Messages
296
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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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 Question
Copy and Paste from one workbook to another...
Replies
6
Views
408
RandyD123
R
H
  • Question Question
Permision Denied-VBA Code to list sub-folders
Replies
4
Views
122
howard
H
E
  • Question Question
Can I eliminate this "File Now Available" -- Read-Write error message?
Replies
9
Views
278
Alex Blakenburg
A
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
134
welshraz
welshraz
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
211
howard
H

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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