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
 
Hey Guys, THANK YOU very much for all your help. Your code(s) worked perfectly and I'm up and running (for now!)

Thanks,

Rob
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
On 2002-03-18 14:57, NateO wrote:
Howdy. I changed the code in my earlier post
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

Hey Guys,

One last question on this same topic. I am working on a slight variation of the code that was given in the quote above. What I would like it to do is take the month in the variant for "nth" (nth = Format(nth, "yyyymmdd")) and automatically add one month to the date.. so if the date it references says February 28, 2002, I want the filename date to return as "200203" since by our definitions, this report would be a "march" report. Confusing I know, but any help here would be appreciated.

Thanks,

Rob
 
Upvote 0
Try the following code:

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

Its not the greatest code but it will work for every month in 2002.
 
Upvote 0
On 2002-03-20 10:14, Al Chara wrote:
Try the following code:

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

Its not the greatest code but it will work for every month in 2002.

Wonderful!

That worked! Another thing I did, just by messing around was to change the "nth = [c3].Value" to "nth = [c3].Value + 2" Trial and error I suppose, but what that appeared to do was add 2 days to the date referenced in cell C3.. so the date in C3 was "February 28, 2002"... and by adding 2 it became "March 2, 2002"... and hence the filename reflected the month of that date.

Does that seem like a logical way to do it? or could I be running into more problems? Anyways, thanks so much for your help.

Rob
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,439
Members
448,573
Latest member
BEDE

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