Freddie has attended:
Excel VBA Introduction course
Opening Prior Month File
Hi Guys
I seemed to have encountered a problem with the below:
FileYear = Year(Date)
strFileMonth = Format(DateAdd("m", -1, Now), "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
Filepath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\*DUMMY*.xlsx"
Workbooks.Open (Filepath)
ActiveWorkbook.SaveAs "C:\Users\richard.heron\Desktop\KPI\Latest KPI FILE.xlsx"
Application.DisplayAlerts = False
It seems when we approach January - it won't go back and look in 2015 for December's file.
The issue stems around the Year(Date). Is there an IF function I can apply to this?
Any help would be greatly appreciated!
Tks
Freddie
RE: Opening Prior Month File
Hi Freddy,
Thank you for the forum question.
Try with a IF DECISION code:
If month(date)=1 then
FileYear = Year(Date)-1
strFileMonth = Format(DateAdd("m", -1, Now), "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
Filepath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\*DUMMY*.xlsx"
Else
FileYear = Year(Date)
strFileMonth = Format(DateAdd("m", -1, Now), "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
Filepath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\*DUMMY*.xlsx"
End If
Workbooks.Open (Filepath)
ActiveWorkbook.SaveAs "C:\Users\richard.heron\Desktop\KPI\Latest KPI FILE.xlsx"
Application.DisplayAlerts = False
What I am doing: If the month is month one I subtract one from the year.
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
