How to save file as next workday???

Posted by Tom Urtis on August 09, 2001 6:54 PM

Hi all:

I have a Sales template file that I save every day as "today's date Sales" in a macro with the following code:

ActiveWorkbook.SaveAs ("C:\My Documents\" & Format(Now(), "mm""-""dd""-""yyyy") & " Sales.xls")

How could I adjust the code for this template to be saved as the next business date instead of today's date, ala the WORKDAY function, including an argument referencing my table in Sheet2 range A1:A10 that lists major holidays so those days are taken into consideration for the save as file date to become the next business workday?

TIA.

Tom Urtis

Posted by Rob Jackson on August 10, 2001 12:14 AM

Change it to this...

If Weekday(Now(), 2) > 4 Then
inc = 8 - Weekday(Now(), 2)
Else
inc = 1
End If
ActiveWorkbook.SaveAs ("C:\My Documents\" & Format((Now() + inc), "mm""-""dd""-""yyyy") & " Sales.xls")

Rob

Posted by Tom Urtis on August 10, 2001 9:28 AM

Thanks Rob, one follow up

Thanks Rob, I'll experiment to add that third argument regarding the date table showing company holidays, but this definitely pointed me in the right direction.

Thanks again.

Tom

Posted by Rob Jackson on August 11, 2001 1:29 AM

Re: Thanks Rob, one follow up

Sorry Chief, try this one...

If Weekday(Now(), 2) > 4 Then
inc = 8 - Weekday(Now(), 2)
Else
inc = 1
End If
for Hol = 1 to 10
if range("Sheet!A" & Hol).value = (Now()+inc) then
inc=inc+1
If Weekday((Now()+inc), 2) > 4 Then
inc = inc +(8 - Weekday(Now(), 2))
end if
end if
next Hol
ActiveWorkbook.SaveAs ("C:\My Documents\" & Format((Now() + inc), "mm""-""dd""-""yyyy") & " Sales.xls")

If the dates are in "A" column of worksheet "Sheet" in cells 1 to 10 in increasing size (future at 10 past at 1) then this should even pick up consecutive holiday dates)

Hope it helps.

Rob Thanks Rob, I'll experiment to add that third argument regarding the date table showing company holidays, but this definitely pointed me in the right direction. Thanks again. Tom : Change it to this... : If Weekday(Now(), 2) > 4 Then

Posted by Tom Urtis on August 11, 2001 3:57 PM

Yo Rob, still not right but I'll try to adjust

Hey Rob, thanks again, except that this doesn't seem to take into consideration files Saved As on a Friday before a 3-day weekend, such as Labor Day weekend; I keep getting the new saved as date being that holiday Monday even with my dates in ascending order in A1:A10 of Sheet2 (not "Sheet" but Sheet2 which I adjusted from your code). I'll keep trying unless you see something in your code that jumps out as not right. Thanks again.

Chief Sorry Chief, try this one...

Posted by Aladin Akyurek on August 12, 2001 9:46 AM

In B1 enter: =TODAY()

In B2 enter: =IF(OR(WEEKDAY(B1)={1,7},ISNUMBER(MATCH(B1,A1:A10,0))),WORKDAY(B1,1,A1:A10),B1)

Incorporate B2 (by concatenating) the filename that you built within your macro.

Aladin

==============