Saving file name with the last business day in YYYYMMDD format

[mozza90]

Hi,

Struggling to save an excel file with the following file name: "20181018 ALEX - Data.xlsx". I have the below code but the application.workday function doesnt seem to be working. Any ideas how to get around this?

File name should be yesterday's business date and then ALEX - Data.

ActiveWorkbook.SaveAs Filename:=  "filepath\" & Format(Application.WorkDay(Date, -1), "YYYYMMDD") & " ALEX - Data" & ".xlsx"

Thanks!

 

[KennyGreens]

Hi, try this:

ActiveWorkbook.SaveAs Filename:=  "filepath\" & Format(VBA.Date - 1, "YYYYMMDD") & " ALEX - Data" & ".xlsx"

 

[Fluff]

In what way isn't it working?

 

[mozza90]

Hi, try this:

ActiveWorkbook.SaveAs Filename:=  "filepath\" & Format(VBA.Date - 1, "YYYYMMDD") & " ALEX - Data" & ".xlsx"

Thanks Kenny, that's nailed it. Been tearing my hair out!

 

[KennyGreens]

Thanks Kenny, that's nailed it. Been tearing my hair out!

No problem friend

 

[Fluff]

Thanks Kenny, that's nailed it. Been tearing my hair out!

Be warned that will give you yesterdays date, not the previous business day.
So if you run that on Monday it will return Sunday's date.

 

[KennyGreens]

Be warned that will give you yesterdays date, not the previous business day.
So if you run that on Monday it will return Sunday's date.

This is absolutely true, I apologize I misread the title.

Here is how to get it for business days:

ActiveWorkbook.SaveAs Filename:=  "filepath\" & Format(CDate(Evaluate("WORKDAY(TODAY(), -1)")), "YYYYMMDD") & " ALEX - Data" & ".xlsx"