Open file when file path contains dates

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for the help in advance

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.

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"

Please Login or Register  to view this content.

WBD

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?

Originally Posted by dangelor

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