98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Last working day of the month
Last working day of the month
Resolved · High Priority · Version 2007
Tue 2 May 2017, 18:17Delegate Freddie said...
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
Tue 2 May 2017, 20:57Trainer Jens said...
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
Tue 9 May 2017: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Convert Text into NumberSome times numbers maybe imported in as text or you maybe concatenating numbers that form a text string that now are treated, because you had to extract them by Text functions |
