Add Date to File Name
 

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?

Ive 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 todays 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

Add Date to File Name
 

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." wrote:

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?

Ive 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 todays 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

Add Date to File Name
 

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 thats why I was getting that annoying error.

Thanks again €“ Jenny B.


"FSt1" wrote:

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." wrote:

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?

Ive 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 todays 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

Add Date to File Name
 

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

"Jenny B." wrote in message
...
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.


"FSt1" wrote:

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." wrote:

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