Opening last working day file of previous month | Excel forum

Hi Freddy,

Thank you for the forum question.

I have copied the code and pasted in one of my workbooks. When I step through the code and follow how the variables get populated I get this:

: strFileDate : "20170929" : Variant/String

The last day of previous month is Saturday 30 September.

Then I changed the date on my laptop to a date in August and stepped the code again and here is what Excel stored in the variable:

: strFileDate : "20170728" : Variant/String

I cannot step the whole code because I do not have the file and the connection you are using. Please open the locals window and step through the whole code and see what you store in the variables. Change the date on your computer and test other months.

The line:
strFileDate = Format(WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date), 0), 0), "ddmmyyyy")

is correct. It will find the last working date of previous month.




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