Add Date to File Name

J

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
 
F

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
 
J

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top