Open file when file path contains dates

Hi there,

Does the following code do what you need?

Option Explicit


Sub OpenReport()

    Const sFILE_PATH__PREFIX    As String = "K:\Reconciliation Team\client\" & _
                                            "Year Ending 2019\" & "FY19 July 19\BOI"

    Const sFILE_PATH__SUFFIX    As String = "312 - EPFX Postings"
    Const sFILE_NAME            As String = "BalanceAndTransactionReport.csv"

    Dim sFullName               As String
    Dim wbkSource               As Workbook
    Dim dteToday                As Date
    Dim wbkCopy                 As Workbook
    Dim sToday                  As String

    Set wbkSource = ThisWorkbook

    dteToday = Date

    Select Case Weekday(dteToday)

           Case vbMonday

                dteToday = dteToday - 3

    End Select

    sToday = Format(dteToday, "dd mmm yy")

    sFullName = sFILE_PATH__PREFIX & "\" & sToday & "\" & sFILE_PATH__SUFFIX & "\" & _
                sFILE_NAME

    If Dir$(sFullName) <> vbNullString Then

          Set wbkCopy = Workbooks.Open(Filename:=sFullName)

    Else: MsgBox "EP report does not exist", vbExclamation, " Missing report"

    End If

End Sub

The highlighted values may be changed to suit your requirements.


Hope this helps - please let me know how you get on.

Greg M