Freddie has attended:
Excel VBA Introduction course
Last working day of the month
Hi Guys,
I have the below code, but I need to make one slight amendment to it.
I want to be able to extract the file for the last working day of the previous month, rather than the last day.
Are you able to help as I am lost on it!
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
Many thanks
Freddie
RE: Last working day of the month
Hi Freddie,
Thank you for the forum question.
The below code should guide you in the right direction. You can only use wildcards for file names in the Dir function.
FileDate = Format(WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date), 1), -1), "ddmmyyyy")
strFileMonth = Format(DateSerial(Year(Date), Month(Date), 0), "yyyymm")
FileYear = Format(DateSerial(Year(Date), Month(Date), 0), "yyyy")
filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\"
Filename = Dir(filepath & "*timedeposit*.xls")
Workbooks.Open (filepath & Filename)
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
