Freddie has attended:
Excel VBA Introduction course
Open file of last day of previous month
Hi GUys
I have the below code, but i want to amend it so that it opens the last file of the previous month - i.e. in this case for example it would be 31/08/2016 - however it is generating the 1/09/1016 file:
If Month(Date) = 1 Then
FileYear = Year(Date) - 1
strFileMonth = Format((DateSerial(Year(Date), Month(Date), 0)), "YYYYMM")
FileDate = Format(Date - 1, "ddmmyyyy")
Filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\*timedeposit*.xls "
Else
FileYear = Year(Date)
strFileMonth = Format(DateAdd("m", 0, Now), "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
Filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\*timedeposit*.xls "
End If
Workbooks.Open (Filepath)
ActiveWorkbook.SaveAs "L:\Macro TestTest.xls"
Application.DisplayAlerts = False
End Sub
I understand I need to change the -1 to zero but it doesnt seem to work?
Freddie
RE: Open file of last day of previous month
Hi Freddie,
Thank you for the forum question.
If I understand what you want the code below should do the job:
Sub Test()
FileDate = Format(DateSerial(Year(Date), Month(Date), 0), "ddmmyyyy")
strFileMonth = Format(DateSerial(Year(Date), Month(Date), 0), "yyyymm")
FileYear = Format(DateSerial(Year(Date), Month(Date), 0), "yyyy")
Filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\*timedeposit*.xls "
Workbooks.Open (Filepath)
ActiveWorkbook.SaveAs "L:\Macro TestTest.xls"
End Sub
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
RE: Open file of last day of previous month
Hi Jens,
Thanks for your feedback on this. However it seems to be extracting the file for 1st Sept. Apologies if i was not clear before, however I was wanting the last file of the month to be opened from 2 months before, i.e the file to be dated 31/08/2016.
Currently I am getting the file dated 01/09/2016?
Also, will the code you provide only pick up working days too?
Many thanks for your help.
Freddie
RE: Open file of last day of previous month
Hi Freddie,
I cannot understand why you get the file dated 01/09/2016.
If you open up the locals window the variable FileDate store the last day of the month not the first day (you have to step the code to see the content in the variables).
I have amended the code to only return the last working day 2 months before the current month.
FileDate = Format(WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date) - 1, 1), -1), "ddmmyyyy")
strFileMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 0), "yyyymm")
FileYear = Format(DateSerial(Year(Date), Month(Date) - 1, 0), "yyyy")
I hope this will do the job.
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
