CODEWITHSUNDEEP
vbaexceldate
user10003190
user10003190

Reputation:

VBA how to account for file name that changes

My workbook "Summary" changes its name daily, as it ends with the date (i.e. Summary 7.2.xlsb). I am trying to open up the last workbook on file, and the code has worked; however, I am not sure how to account for weekends since the - 1 does not apply anymore. If you need me to be more specific please let me know.

Upvotes: 0

Views: 35

Answers (1)

PatricK
PatricK

Reputation: 6433

You just need a Date counter to decrease until it's Mon-Fri.

Sub OpenPreviousWorkdayFile()
    Const filepath = "\\FileShare\work\"
    Dim wb As String
    Dim isum As Workbook
    Dim dWorkDate As Date

    dWorkDate = Date
    Do
        dWorkDate = dWorkDate - 1
    Loop Until Weekday(dWorkDate, vbMonday) < 6 ' Mon=1,Tue=2,etc

    wb = "Summary " & Format(dWorkDate, "m.dd") & ".xlsb"
    Set isum = Workbooks.Open(filepath & wb)
End Sub

Upvotes: 1

Related Questions