Open file when file path contains dates

[heathb]

Hi all,

Having an issue getting the correct logic to open a file path that changes each month and year, examples below and start of effort.

I am checking if the file exists, and if so, then open

The date at the end of the path needs to be fool proof, so 8 July (d mmm) as well as 08 July (dd mmm)

The FY20 stands for Full Year 2020 which changes monthly, i.e. we are in July now.

K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\8 July 19
K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\28 July 19
K:\Reconciliation Team\client\Year Ending 2020\FY20 Jan 20\BOI\8 Jan 20

Dim wbCopy As Workbook
Dim TDate As Date

Dim wbMe As Workbook
Dim trans As Integer
Set wbMe = ThisWorkbook


Dim OHS As String
OHS = ""

On Error Resume Next

TDate = Date

Select Case Weekday(TDate)

Case vbMonday
    TDate = TDate - 3
    End Select

'OHS = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\8 July 19\312 - EPFX Postings\BalanceAndTransactionReport.csv"")"

OHS = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\format (tdate, dd mmm yy)\312 - EPFX Postings\BalanceAndTransactionReport.csv"")"

On Error GoTo 0
If OHS = "" Then

MsgBox "EP report does not exist"

Exit Sub

End If
Application.DisplayAlerts = False
    

Set wbCopy = Workbooks.Open("K:\Reconciliation Team\Baydonhill\Year Ending 2019\FY19 July 19\BOI\format (date, dd mmm yy)\312 - EPFX Postings\BalanceAndTransactionReport.csv")

Thanks for the help in advance

[CK76]

Well... your issue is that folder naming convention isn't consistent.

8 July 19. Is not "dd mmm yy" format, but "dd mmmm yy". However, Jan is in "dd mmm yy" format.

You'll either have to check for both patterns or make sure folders use consistent format.

[WideBoyDixon]

May need some more help on how you decide:

- Which financial year we are in (i.e. when does it start - January? April?)
- When you use the full month name (e.g. July) vs. the abbreviation (e.g. Jan)

For example, the below will generate OK for July but not for January if you're using "Jan"

Dim wbCopy As Workbook
Dim TDate As Date

Dim wbMe As Workbook
Dim trans As Integer
Set wbMe = ThisWorkbook

Dim OHS As String

On Error Resume Next

TDate = Date
If Weekday(TDate) = vbMonday Then TDate = TDate - 3

OHS = "K:\Reconciliation Team\client\Year Ending " & Format(TDate, "yyyy") & "\FY" & Format(TDate, "yy mmmm yy") & "\BOI\" & Format(TDate, "d mmmm yy") & "\312 - EPFX Postings\BalanceAndTransactionReport.csv"

On Error GoTo 0
If Dir(OHS) = "" Then
    MsgBox "EP report does not exist"
    Debug.Print OHS
    Exit Sub
End If

Application.DisplayAlerts = False

Set wbCopy = Workbooks.Open(OHS)

WBD

[Greg M]

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

[dangelor]

Another possible solution (bold = changes)...

Dim wbCopy As Workbook
Dim TDate As Date

Dim wbMe As Workbook
Dim trans As Integer
Set wbMe = ThisWorkbook


'Dim OHS As String
'OHS = ""

On Error Resume Next

TDate = Date

Select Case Weekday(TDate)

Case vbMonday
    TDate = TDate - 3
End Select
    
Dim sDate1 As String, sDate2 As String
sDate1 = Format(TDate, "dd mmm yy")
sDate2 = Format(TDate, "d mmm yy")

Dim OHS1 As String, OHS2 As String
OHS1 = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\" & sDate1 & "\312 - EPFX Postings\BalanceAndTransactionReport.csv"
OHS2 = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\" & sDate2 & "\312 - EPFX Postings\BalanceAndTransactionReport.csv"

'OHS = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\8 July 19\312 - EPFX Postings\BalanceAndTransactionReport.csv"")"

'OHS = "K:\Reconciliation Team\client\Year Ending 2019\FY19 July 19\BOI\format (tdate, dd mmm yy)\312 - EPFX Postings\BalanceAndTransactionReport.csv"")"

On Error GoTo 0
'If OHS = "" Then
'
'MsgBox "EP report does not exist"
'
'Exit Sub
'
'End If
'Application.DisplayAlerts = False
'
'
'Set wbCopy = Workbooks.Open("K:\Reconciliation Team\Baydonhill\Year Ending 2019\FY19 July 19\BOI\format (date, dd mmm yy)\312 - EPFX Postings\BalanceAndTransactionReport.csv")
If Len(Dir(OHS1)) = 0 Then
    If Len(Dir(OHS2)) = 0 Then
        MsgBox "EP report does not exist"
        Exit Sub
    Else
        Set wbCopy = Workbooks.Open(Filename:=OHS2)
    End If
Else
    Set wbCopy = Workbooks.Open(Filename:=OHS1)
End If

[heathb]

Thanks all, however none of the above finds the file, just opens the message box

Debug tells me the Expression YEAR is not defined!

Should it be in quotes?

[heathb]

Another possible solution (bold = changes)...

Have got the date working, so it is looking at OHS2 correctly, however, when it looks at Len(Dir(OHS))=0 it goes to message box

Have edited the code to be mmmm rather than mmm

Unfortunately, I cannot see the whole of the path when hovering over it.

When I use "K:\Reconciliation Team\Client\Year Ending 2019\FY19 July 19\BOI\" & Format(TDate, "d mmmm yy") & "\312 - EPFX Posting\BalanceAndTransactionReport.csv"
The report opens correctly

[dangelor]

Try this one...

Sub test()
    Dim wbCopy As Workbook
    Dim TDate As Date
    Dim wbMe As Workbook
    Dim trans As Integer
    Dim OHS1 As String, OHS2 As String
    Dim sDate1 As String, sDate2 As String
    Dim sDate3 As String, sDate4 As String
    
    Dim sYear1 As String, sYear2 As String
    Dim sYear3 As String
    Dim x As Long, y As Long
    Dim sDate As Variant, sYear As Variant
    
    Set wbMe = ThisWorkbook
    TDate = Date
    
    Select Case Weekday(TDate)
        Case vbMonday
            TDate = TDate - 3
    End Select
    
    sDate1 = Format(TDate, "dd mmm yy")
    sDate2 = Format(TDate, "d mmm yy")
    sDate3 = Format(TDate, "dd mmmm yy")
    sDate4 = Format(TDate, "d mmmm yy")
    sYear1 = "\FY" & Format(TDate, "yy mmm yy")
    sYear2 = "\FY" & Format(TDate, "yy mmmm yy")
    sYear3 = Format(TDate, "yyyy")
    OHS1 = "K:\Reconciliation Team\client\Year Ending "
    OHS2 = "\312 - EPFX Postings\BalanceAndTransactionReport.csv"
    sDate = Array(sDate1, sDate2, sDate3, sDate4)
    sYear = Array(sYear1, sYear2)
    For x = 0 To 3
        For y = 0 To 1
            If Len(Dir(OHS1 & sYear3 & sYear(y) & "\BOI\" & sDate(x) & OHS2)) <> 0 Then
                Set wbCopy = Workbooks.Open(Filename:=OHS1 & sYear3 & sYear(y) & "\BOI\" & sDate(x) & OHS2)
            End If
        Next y
    Next x
    If wbCopy Is Nothing Then
        MsgBox "EP report does not exist"
        Exit Sub
    End If
End Sub

[heathb]

Hi Dangelor,

Still cannot find the file

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated

Best regards,

Greg M

[heathb]

I have it working finally, many thanks for your help and putting up with me Expect any errors was down to myself

the following works

Dim wbCopy As Workbook
Dim TDate As Date

Dim wbMe As Workbook
Dim trans As Integer
Set wbMe = ThisWorkbook

On Error Resume Next

TDate = Date 

Select Case Weekday(TDate)

Case vbMonday
    TDate = TDate - 3

   End Select
  
Dim sDATe1 As String, sDAte2 As String

sDATe1 = Format(TDate, "dd mmmm yy")
sDAte2 = Format(TDate, "d mmmm yy")

Dim OHS1 As String, OHS2 As String
OHS1 = "K:\Reconciliation Team\Client\Year Ending " & Format(TDate, "yyyy") & "\FY" & Format(TDate, "yy ") & Format(TDate, "mmmm yy") & "\BOI\" & sDATe1 & "\BalanceAndTransactionReport.csv"
OHS2 = "K:\Reconciliation Team\Clientl\Year Ending " & Format(TDate, "yyyy") & "\FY" & Format(TDate, "yy ") & Format(TDate, "mmmm yy") & "\BOI\" & sDAte2 & "\BalanceAndTransactionReport.csv"



On Error GoTo 0
If Len(Dir(OHS1)) = 0 Then
    If Len(Dir(OHS2)) = 0 Then
        MsgBox "EP report does not exist"
        Exit Sub
    Else
        Set wbCopy = Workbooks.Open(Filename:=OHS2)
    End If
Else
    Set wbCopy = Workbooks.Open(Filename:=OHS1)
End If

[dangelor]

Glad that I was of some help. Thanks for letting me know!