VBA to open filepath where year and month change

[tonyjyoo]

Hello,

I need some assistance with VBA to open a file where the year, month, and days change on a daily basis.

The filepath will be as such:

S:\NPH Accounting\NPH Accounting Department\Treasury\2016\5 NPH FundServ\01 Daily\DTCC Settlement Reports Daily\05 Current Day DTCC Settlement\05 NPH\12 DTCC Current Day\NPH DTCC 12.19 CD MF.xlsx"

- So the "2016" after Treasury will change on a yearly basis
- The month (in "12 DTCC Current Day" segment) will change on a monthly basis
- and the file name "NPH DTCC 12.19.CD MF.xlsx" will change on a daily basis.

Can someone help me construct this into VBA? I will be using this to run a macro every day, so it would be convenient.

Thanks in advance!!!!

 

[Scott Huish]

This would give you that filepath based on the current date:

Sub test()
Dim dt As Date
dt = Date
filepath = "S:\NPH Accounting\NPH Accounting Department\Treasury\" & Format(dt, "yyyy") & "\5 NPH FundServ\01 Daily\DTCC Settlement Reports Daily\05 Current Day DTCC Settlement\05 NPH\" & Format(dt, "mm") & " DTCC Current Day\NPH DTCC " & Format(dt, "mm.dd") & " CD MF.xlsx"
MsgBox filepath
End Sub