how to open file with yesterday's workday in title

Question

i need to open a file with yesterday's workday in the title and i have no idea how to do it.

Eg Monday (2nd sep) today, open Friday's (30th Aug) spreadsheet.

The spreadsheet title is a follows - "cash 300819"

I have tried the following code but doesnt seem to work

Dim wbO As Workbook, wbN As Workbook

Set wbO = ActiveWorkbook
Set wbN = Workbooks.Open("\\y:cash " & Format(CStr(Date)-1, "dd") & CStr(Format(Date, "mm")) & Right(CStr(Year(Date)), 4) & ".xlsx")

It will open previous working day's spreadsheet properly

You can use `WorksheetFunction.WorkDay` to get the previous work day. There may be better examples of its use but here is [one question](https://stackoverflow.com/questions/49512481/vba-if-previous-day-is-a-bank-holiday-open-file-from-previous-working-day) that could be helpful. — BigBen, Sep 04 '19 at 02:33

braX · Accepted Answer · 2019-09-04T04:27:27.203

You can use the DateAdd function in VBA to subtract a day, like this:

sFilename = "\\y:cash " & Format(DateAdd("d", -1, Date), "dd") & Format(Date, "mm") & Right(Year(Date), 2) & ".xlsx"

And you dont need the CStr function, as the format and right functions already return strings.

But what about the first day of the month? It would be better to do it like this:

sFilename = "\\y:cash " & Format(DateAdd("d", -1, Date), "ddmmyy") & ".xlsx"

Alternatively, you can have a function that would return an empty string if no file was found, or return the filename of the most recent file based on your criteria, like this:

Public Function GetMostRecentFileByDate(dtStart As Date, sPath As String, sPrefix As String, sExt As String, sFormat As String) As String
  Dim nDay As Integer
  Dim sFilename As String
  Dim dtDate As Date
  Dim sFull As String

  dtDate = dtStart
  For nDay = -1 To -7 Step -1
    sFilename = sPrefix & Format(dtDate, sFormat) & "." & sExt
    sFull = sPath & "\" & sFilename
    If Dir(sFull) <> "" Then
      GetMostRecentFileByDate = sFull
      Exit Function
    End If
    dtDate = DateAdd("d", -1, dtDate)
  Next

End Function

Usage:

sFullName = GetMostRecentFileByDate("03 Sept 2019", "\\y:", "cash ", "xlsx", "ddmmyy")
If sFullName <> "" Then
  ' Do Something With It
End If

does this work for working days? For example Monday-1 being previous Friday — Xkluah89, Sep 04 '19 at 02:47
That assumes everyone works monday to friday, and not everyone does. — braX, Sep 04 '19 at 02:49
Ideally, I would make the -1 a variable that decreases in a loop to determine a filename to check for, and go back 7 days. That would find the "most recent previous day worked" file, if one exists. Check for yesterday, if found, use that, if not found, check for a file the day before that... and so on... — braX, Sep 04 '19 at 03:29

how to open file with yesterday's workday in title

1 Answers1