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

Mopacs

New Member
Joined
Mar 6, 2002
Messages
33
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
The following procedure combines your constant with the date of last saturday based on todays date:
Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by nateo on 2002-03-18 14:56
 
Upvote 0
On 2002-03-18 13:14, NateO wrote:
The following procedure combines your constant with the date of last saturday based on todays date:
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-18 17:10
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top