Open file with variable date

[RandyD123]

All,

I am trying to use a macro that will open a file in a folder called "DOR". This file could exist in that folder or a subfolder (DOR/Archived) or in a monthly folder, that changes each month (2022.11) or (2022.12) and so on.

The filename will be in the same format every day, "DO Report 11-09-22". So tomorrow the filename will be "DO Report 11-10-22". I need to open this file no matter what subfolder of the main DOR folder it is in.

The macro has to open YESTERDAY'S file. So when I click the macro button the code will open yesterdays file.

This is what I have so far. I really don't need the msg box but if it stays I'm ok with it.

Sub OpenWorkbook()
    Dim sYesterday As String, sFileName As String

    sYesterday = Format(Now - 1, "MM-DD-YY")
    MsgBox sYesterday
    
    sFileName = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR/DO Report " & sYesterday & ".xlsm"
    
    MsgBox sFileName
    
End Sub

The only thing I get is the msg box but the file doesn't open. Like I said, I don't need the msg box, I just need yesterdays file to open no matter what subfolder it is in. Thanks in advance.

 

[rlv01]

Your macro is missing an 'Open' statement, so it won't open the file. You need to add Workbooks.Open sFileName.

Not tested.

Sub OpenWorkbook()
    Dim sYesterday As String, sFileName As String

    sYesterday = Format(Now - 1, "MM-DD-YY")
    MsgBox sYesterday
    
    sFileName = "https://airport.ishare.tsa.dhs.gov/fieldlocations/MHT/soc/SOC Scheduling/Shared Documents/DOR/DO Report " & sYesterday & ".xlsm"
    
    Workbooks.Open sFileName
    
    MsgBox sFileName
    
End Sub