Help! Looking for a macro to open a file whose name changes every week

[nlmtz80]

Hi All,

I am trying to write a macro which opens a spreadsheet, whose name has a prefix which stays the same, but the date (mm.dd.yy) changes every week with the following format: ON_mm.dd.yy.xlsx

I.e This weeks name is 'ON_08.08.18.xlsx' and last weeks name is 'ON_08.01.18.xlsx'.

The report is created every Wednesday and refers to the previous weeks reports.

Thanks!

 

[JumboCactuar]

this works for me, adjust as needed:

place formula in cell A1 to get Wednesday date of the current week

=CONCATENATE("ON_",TEXT(TODAY()-WEEKDAY(TODAY(),3)+2,"mm.dd.yyyy"))

and macro

Sub OpenWB()
Dim WB2 As Workbook
Dim MyURL As String
Dim MyFile As String


    MyPath = "c:\testing\"
    MyURL = Range("A1").Value
    MyFile = MyPath & MyURL & ".xlsb"


    Set WB2 = Workbooks.Open(MyFile)


End Sub

 

[nlmtz80]

Thank you, however I need to open the file from the Wednesday date of the PREVIOUS week

 

[silentwolf]

Hi,
Change the Formular as below ..

=CONCATENATE("ON_",TEXT(TODAY()-WEEKDAY(TODAY(),3)-5,"mm.dd.yyyy"))

I believe this should do it..