Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

last working day month

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

ResolvedVersion 2007

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

Tue 9 May 2017: Automatically marked as resolved.

 

Training courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Use the Ctrl-key for quick navigation in Excel 2010

If you want to move quickly to the right, left, top or bottom of your spreadsheet, just press Ctrl and one of the arrow keys. If you want to then select all the data in that particular row or column, hold down the Shift key and press Ctrl and an arrow key.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.29 secs.