Workbook open using IShare Path

[RandyD123]

Path to my sheets - "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR"

DOR Folder is the top folder where my sheets will be found. Sub Folders are "Archived" and a monthly folder "2022.10" (that changes each month before being moved to the "Archived" folder.

What I need to get is the excel file dated the day before the current day. So if today is 10/15/22 I need to get the file named "DO Report 10-14-22". A new file is created each day. So tomorrow I will have a file named "DO Report 10-15-22" and so on.

This file could be in any of the 3 folders. ie: DOR, 2022.10, Archived. Next month one of the folders will change to 2022.11 and so on.

How do I do this using VBA? Thanks for any help.

 

[MCLIFTO8]

Since you know the document name and one of it's three locations, just try each of the three locations and wrap them in an "On Error GoTo" statement.
This is a pretty bad way of handling errors but since you haven't received an answer yet it should point you in the right direction.

eg

Sub openDOReport()
    Dim wbMyWorkbook As Workbook
    Dim strWBName As String
    Dim strWBPathStub As String
    Dim strWBPath As String

    strWBName = "DO Report " & Format(DateAdd("d", -1, Date()), "yyyy-dd-mm") & ".xlsx" '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-dd-mm")
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub 

   'Attempt 3
   strWBPath = strWBPathStub & "/Archive"
   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

 

[RandyD123]

Since you know the document name and one of it's three locations, just try each of the three locations and wrap them in an "On Error GoTo" statement.
This is a pretty bad way of handling errors but since you haven't received an answer yet it should point you in the right direction.

eg

Sub openDOReport()
    Dim wbMyWorkbook As Workbook
    Dim strWBName As String
    Dim strWBPathStub As String
    Dim strWBPath As String

    strWBName = "DO Report " & Format(DateAdd("d", -1, Date()), "yyyy-dd-mm") & ".xlsx" '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-dd-mm")
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub

   'Attempt 3
   strWBPath = strWBPathStub & "/Archive"
   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

This is what I got to work. Thank you for your help.

Sub openDOReport()

    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), "mm-dd-yy")
   On Error Resume Next
   Set wbMyWorkbook = Workbooks.Open(strWBPath & "\" & strWBName)
   If Not wbMyWorkbook Is Nothing Then Exit Sub

   'Attempt 3
   strWBPath = strWBPathStub & "/Archive"
   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

 

[RandyD123]

Oppps, spoke too soon. So it seems that if my file is in the actual "DOR" Folder it will find it. If my file is in the "Archive" Folder it will find it. But if it's in the monthly folder....(ie: 2022.11) it won't find it. That folder changes each month......2022.12, then it will start with 2023.01.....and so on..... After each month those monthly folders move to the Archive folder.

 

[MCLIFTO8]

So attempt 2 is failing. Probably because we wrote "yyyy-dd-mm" instead of "yyyy.mm"

Instead of writing

strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date()), "yyyy-dd-mm")

try writing

strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date()), "yyyy.mm")

If that doesn't work modify your

Format(DateAdd("d", -1, Date()), "yyyy-dd-mm")

line until you get it to match up with the folder name.

An easy way to do this is to go into the VBA editors immediate window and type

Debug.Print Format(DateAdd("d", -1, Date()), "yyyy.mm")

and play around with that until you get the correct format.

 

[RandyD123]

So attempt 2 is failing. Probably because we wrote "yyyy-dd-mm" instead of "yyyy.mm"

Instead of writing

strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date()), "yyyy-dd-mm")

try writing

strWBPath = strWBPathStub & "/" & Format(DateAdd("d", -1, Date()), "yyyy.mm")

If that doesn't work modify your

Format(DateAdd("d", -1, Date()), "yyyy-dd-mm")

line until you get it to match up with the folder name.

An easy way to do this is to go into the VBA editors immediate window and type

Debug.Print Format(DateAdd("d", -1, Date()), "yyyy.mm")

and play around with that until you get the correct format.

Yep, that was the problem. Thank You!!