VBA to open file where Path Year and Month will change
Posted on 2014-03-20
I am using the below vba code that was supplied by robhenson to open files where the Year and date of the file changes. Howver I now need to add a further dimension to this to include the Month in the path [03 Mar, 04 Apr etc] I have tried to make the change myself but have faild miserably. Can an expert out there please provide the answer.
i.e. path is G:\Sales\Recon\Results\2014\03 Mar\dd.mm.yyyy.xls
The code i have is
FileYear = Year(Date)
MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "dd.mm.yyyy")
FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & FileDate & ".xls"