VBA Save File As Previous Work Day

[jtbrolly]

Two questions:

I'm using the following to save a file down as today's date:

ActiveWorkbook.SaveAs Filename:="Name" & _
    Format(Now(), "mm-dd-yyyy"), FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

But I'm trying to figure out how to do two other things for a different file.

First, how can I save it as yesterday's date, specifically yesterday's business date so I don't have to change it on Monday's.

And secondly, how do I save it with the date in the middle of two static words.

Much obliged!

 

[sykes]

The two static words bit would be:

ActiveWorkbook.SaveAs Filename:="File saved on " & _
    Format(Now(), "mm-dd-yyyy") &  " by A.User", FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Not entirely sure what you mean by busines date, but to save as yesterday's date I think you should be able to replace the above section:

Format(Now()

with...

Format(Now()-1

...so the whole lot would look thus:

ActiveWorkbook.SaveAs Filename:="File saved on " & _
    Format(Now()-1, "mm-dd-yyyy") &  " by A.User", FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

NB not tested, but it should work.

 

[sykes]

Please let us know whether or not this helped.

 

[VoG]

Hello sykes

Maybe

x = Weekday(Date, vbSaturday)
Select Case x
    Case 1
        x = 2
    Case 2
        x = 3
    Case Else
    x = 1
End Select
ActiveWorkbook.SaveAs Filename:="File saved on " & _
    Format(Date - x, "mm-dd-yyyy") & " by A.User", FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

 

[sykes]

Hi Peter

Looks like a good solution. Hope it suits the OP.

Sykes

 

[jtbrolly]

Hey sorry, was away for the weekend and didn't check computer. I'll try these solutions now, thanks!

 

[jtbrolly]

I just tried it for today and it saved it as the yesterday, the 18th, which is a Sunday. I'm not too familiar with this formula so I'll investigate it furhter, but I tried changing it to:

x = Weekday(Date, vbSunday)

instead of

x = Weekday(Date, vbSaturday)

And it worked for today, so I'll see if it works tomorrow.

 

[jtbrolly]

The change seemed to work! Thanks so much to both of you!

Cheers!

 

[Bishop346]

*cast a bit of forum necromancy* I don't normally post to old forums but this has been a useful function at work.

I have come across a useful Excel formula that I use periodically for my reports:

=WORKDAY(TODAY(),-1)

This will give you the previous workday. If you include the option [Holidays] parameter, it will even account for that in the internal workday calculation.

WORKDAY(start_date, days, [holidays])

The benefit is that this function will wrap around the weekends and holidays to give you the previous workday datevalue (if you use negative {days}) or the next workday datevalue (if you use positive {days}).