Opening last working day file of previous month | Excel forum

Resolved · High Priority · Version 2010

Wed 4 Oct 2017, 09:04 Delegate Freddie said...

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

Fri 6 Oct 2017, 09:39 Trainer Sarah said...

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

Thu 12 Oct 2017, 10:17 Delegate Freddie said...

RE: Opening last working day file of previous month

Hi Guys,

Any update on this?

Thanks!

Freddie

Thu 12 Oct 2017, 14:23 Trainer Jens said...

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

Thu 19 Oct 2017: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

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

Excel tip:

Ctrl+d's double life

Suppose I have a formula in B1 that I wish to copy into B2:B10. I can select B1:B10 then press Ctrl+d to copy the formula down the selected range. Users generally ignore this shortcut in favour of double-clicking on the fill handle to copy down, but Ctrl+d is useful sometimes particularly when there is no data in surrounding columns to guide to how far the double-click method should copy formulae.

Ctrl+d has another use though. When I use the drawing toolbar to draw objects such as Text Boxes, Rectangles and Ovals onto a worksheet, Ctrl+d makes an instant duplicate of selected shapes. For example, I need five Text Boxes the same size. I draw one Text box and adjust it to the size I want, select it, then press Ctrl+d four times to get four identical copies.

View all Excel hints and tips