Freddie has attended:
Excel VBA Introduction course
Opening last working day file of previous month
VBA Intermediate Excel:
Hi Guys,
I have the below code, but I am trying to ensure that it opens the last working day's file in the event the last day of the previous month falls on a weekend.
If Month(Date) = 1 Then
FileYear = Format(DateSerial(Year(Date), Month(Date), 1), "yyyy")
strFileMonth = Format((DateSerial(Year(Date), Month(Date), 1)), "YYYYMM")
strFileDate = Format(WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date), 0), 0), "ddmmyyyy")
FilePath = "\\123.40.0.42\DailyReports\" & FileYear & "\" & strFileMonth & "\" & 6115 & "\" & strFileDate & "*_cd_*.xls"
Else
FileYear = Format(DateSerial(Year(Date), Month(Date), 0), "yyyy")
strFileMonth = Format((DateSerial(Year(Date), Month(Date), 0)), "YYYYMM")
strFileDate = Format(WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date), 0), -1), "yyyymmdd")
FilePath = "\\123.40.0.42\DailyReports\" & FileYear & "\" & strFileMonth & "\" & 6115 & "\" & strFileDate & "*_cd_*.xls"
FileName = Dir(FilePath & "*_cd_6115*" & ".xls")
Workbooks.Open (FilePath & FileName)
End If
Any ideas?
Thanks!
Freddie
RE: Opening last working day file of previous month
Hi Freddie
Thanks for your question!
Our VBA trainers are currently training, so to give you the very best answer we will get back to you on Monday when they are back in the office.
Thanks
Sarah
Excel Trainer
RE: Opening last working day file of previous month
Hi Guys,
Any update on this?
Thanks!
Freddie
RE: Opening last working day file of previous month
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
