Save As name

[Guest]

I need to save a file with the following parameters. "Location 8/12/08" The
"Location" will be a static value but the date needs to be the sunday for the
previous week.

Thanks

 

[Guest]

Greg,

You won't be able to include the slash character as part of your filename,
so maybe use the underscore character.

Sub test()
Dim dtPreviousSunday As Date
Dim i As Integer

i = Application.WorksheetFunction.Weekday(Date)
If i = 1 Then
dtPreviousSunday = Date - 7
Else
i = i - 1
dtPreviousSunday = Date - (i + 7)
End If

ThisWorkbook.SaveAs "Z:\TEMP\Location " & Format(dtPreviousSunday, "mm_dd_yy")

End Sub

 

[Guest]

Works perfectly, thanks so much!

Greg,

You won't be able to include the slash character as part of your filename,
so maybe use the underscore character.

Sub test()
Dim dtPreviousSunday As Date
Dim i As Integer

i = Application.WorksheetFunction.Weekday(Date)
If i = 1 Then
dtPreviousSunday = Date - 7
Else
i = i - 1
dtPreviousSunday = Date - (i + 7)
End If

ThisWorkbook.SaveAs "Z:\TEMP\Location " & Format(dtPreviousSunday, "mm_dd_yy")

End Sub

 

[Dana DeLouis]

i = Application.WorksheetFunction.Weekday(Date)
In Vba, another technique is to switch the week reference to the following
day (ie Monday)

dtPreviousSunday = Date - Weekday(Date, vbMonday)