How to Save a Date into the Filename of a spreadhseet?

[Mopacs]

Hello again,

I am looking for macro code that will automatically insert a user-specified date (in yyyymmdd format) into an XL filename.

For instance, a typical filename would be "Reg 05_Weekly_20020209.xls" where the "Reg05_Weekly_" portion would remain constant, but the date "20020209" would have to reflect the date specified by the person running the macro. I would probably want to insert an "input Box" where the user would be prompted to enter this past Saturday's date in "yyyymmdd" format.

Any suggestions on how to pull this off?

Any assistance here would be greatly appreciated.

Thanks,

Rob

[Al Chara]

Try the following code:

ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(ThisWorkbook.Name, ".xls", "_") & WorksheetFunction.Substitute(Date, "/", "")

Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-03-18 13:05 ]

[Mopacs]

On 2002-03-18 12:12, Al Chara wrote:
Try the following code:

ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(Date, "/", "")

Hope this helps.
Kind regards, Al.

Cool! Thats definitely what I had in mind.. only thing is, how do I append this date to the end of a constant("Reg_01_") so that the renamed file is now named "Reg_01_20020316.xls", etc.?

Your help is greatly appreciated

Rob

[Al Chara]

Try the updated code in my earlier post.

Hope this helps.
Kind regards, Al.

[NateO]

The following procedure combines your constant with the date of last saturday based on todays date:

Sub LastSat()
Dim nth As Variant, last As Integer
last = WeekDay(Date)
nth = Format(Date - last, "yyyymmdd")
ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
End Sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-03-18 14:56 ]

[Mopacs]

On 2002-03-18 13:14, NateO wrote:
The following procedure combines your constant with the date of last saturday based on todays date:

Sub LastSat()
Dim nth As Variant
nth = DateSerial(year(Date), Month(Date), (8 - Weekday(DateSerial(year(Date), Month(Date), 1), _
     (7 + 1) Mod :cool:) + ((3 - 1) * 7))
nth = Format(nth, "yyyymmdd")
ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
End Sub

Wow, its getting there! That worked too. I understand your logic, but still having a little trouble following...

Really what I'd like to do is just have the macro reference a cell in this worksheet (to be renamed) which already has the date specified (the user specifies the date already in an earlier macro step). In this case its cell "C3"..and contains the "Past saturday's" date in "Month Day, Year" format (Ie, March 16, 2002)..

I would like to just have the macro reference that cell, change the date formatting so that is appears as "20020316" and then append that date to the end of "Reg1_" , as before.

Any suggestions here?

Thanks for your patience (all of you).

Rob

[Al Chara]

Try the following code:

ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(ThisWorkbook.Name, ".xls", "_") & Format(Range("c3").Value, "yyyymmdd")

This code takes the the value in cell C3 (assumes it is in date format mm/dd/yyyy) and changes it to yyyymmdd and then adds it to the existing file name. I assumed that the file name was already Reg 05_Weekly.xls. Is this true, or do you want the Reg 05_Weekly_ generated also?

[NateO]

Try This:

Sub LastSat2()
Dim nth As Variant
nth = [c3].Value
nth = Format(nth, "yyyymmdd")
ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
End Sub

Change "Reg_01_" to whatever you want your constant to be.

HTH.

[Mopacs]

Hmm..having a little trouble.. it seems to ignore the date and only places the "Reg_01_" part in the filename. Well I must leave for the day but I will pick right up here tomorrow morning.

Thanks again for your help

Rob

[NateO]

Howdy. I changed the code in my earlier post as the value 3 was a plugged value for the 3rd Sat. in the month, wouldn't have worked next week. But, I revised the post, it should work any week. You may want to reconsider having the user enter last saturday, might just be easier to have VBA do this for you like above. Less room for error.

However, you're the boss. If you want cell c3, make sure c3 has a date in it, like below:

Sub LastSat3()
Dim nth As Variant
nth = [c3].Value
If IsDate(nth) Then
nth = Format(nth, "yyyymmdd")
Else: MsgBox ("Invalid Date" & Chr(13) & Chr(13) & "Check cell C3")
Exit Sub
End If
ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
End Sub

Hope this helps.
________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-18 17:10 ]