Opening Files in file path but being able to change the date on new workdays

Question

I open multiple files at my job and would like to know how to open different files by concatenating a workday variable into the file path. Is this possible?

Code:

Sub File_Demo()

Dim pathname
Dim Date_X
Dim Date_Y


Date_X = “02.01.22”
Date_Y = 20220201

Pathname = 
“C:\Users\Jinx\Desktop\PDF_Files\2022\Feb\& Date_X &\PDF_FileName & Date_Y &.pdf”

ActiveWorkbook.FollowHyperlink pathname

End Sub

I would use this code to open multiple files at once by using a workday - 1, -2, -3, etc type of variable but am stuck on where to start in this process.

Answer 1

The Date Keyword can be used in VBA to get the current date value. The Format function can then turn the date value into whatever format you supply. On that article I linked above, they give a useful chart of how to create a date format.

Format(Date, "mm.dd.yy") would be "02.18.22" today and "02.19.22" tomorrow.

Format(Date, "yymmdd") would be "220218" today and "220219" tomorrow.

If you need pieces of the current date, like the Year or the Month you can use the aptly named Year and Month functions. Year(Date) would return 2022. And Month(Date) would return 2. If you need the month as a three-letter abbreviation, Format(Date, "mmm") would give you "Feb".

So for your file path you can do:

Pathname = "C:\Users\Jinx\Desktop\PDF_Files\" & Year(Date) & "\" & Format(Date, "mmm") & "\" & Format(Date, "mm.dd.yy") & "\PDF_FileName" & Format(Date, "yymmdd") & ".pdf"

If you need to change the date, and re-build the pathname, simply replace Date with a different value. You can even have a loop like

'From yesterday to three days from now
For myDate = Date - 1 to Date + 3
    Pathname = "C:\Users\Jinx\Desktop\PDF_Files\" & Year(myDate) & "\" & Format(myDate, "mmm") & "\" & Format(myDate, "mm.dd.yy") & "\PDF_FileName" & Format(myDate, "yymmdd") & ".pdf"
    'Do Stuff
Next

Notes: I also noticed that the quotation marks in your post were not standard " marks. That will cause an error in VBA since they will register as unrecognized symbols. Additionally, strings must be concatenated with & symbols, but those must be outside the quotation marks.

Eg. myString = "abc" & "def"

NOT myString = "abc&" "def"