Last working day of the month

Resolved · High Priority · Version 2007

Tue 2 May 2017, 18:17 Delegate 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:57 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Convert Text into Number

Some 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

To convert Text into Number just encase the relevant cell reference or formula in the TEXT function. See Converting American Date to European hint

eg TEXT(Ref) or TEXT(formula)

View all Excel hints and tips