Add Date to File Name

[Jenny B.]

Hi All,
I use the below to save a copy of my worksheet to my desktop before its
contents are cleared. I would like to add a now() or today() date stamp to
the fName. Any ideas how to do that?

I’ve combined the ranges on my excel spreadsheet cell H5 with a text date
style =H5& " "&TEXT(TODAY(),"mm/dd/yy"). When I try to add this to the
macro below, I get this error –
Run-Time error 1004.

Any thoughts on how I can have the output file contain my H5 value which is
text and today’s date?

Thank you – Jenny B.


Sub Save()

Dim myPath As String
Dim nRng As Range
Dim fName As String
Set nRng = Range("H5")

ActiveSheet.Copy

Call DeleteAllCode
ActiveSheet.Shapes("Send").Visible = False
myPath = "C:\Documents and Settings\Name Here\Desktop\"
fName = nRng.Value & ".xls"

ActiveWorkbook.SaveAs filename:= _
myPath & fName, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call CLEAR
ActiveSheet.Visible = False

End Sub

 

[FSt1]

hi
wild guessing here.
slashes are forbidden as a file naming conviction. no slashes in a file name.
try this....
style =H5& " "&TEXT(TODAY(),"mmddyy")
your date will end up looking like this......062108.
i use this style when i'm adding a date to a file name. mainly because
slashes are forbidden.

regards
FSt1

 

[Jenny B.]

Thank your for you help. You were right on the money and it now works great.
I should have known better and remembered that the slashes represent a path
and that’s why I was getting that annoying error.

Thanks again – Jenny B.

 

[Rob L]

As well, if you format your date as yymmdd, (or yyyy mm dd as I do) then the
files will sort in the right time order....

OM