Command Button Question

[Todd D. Levy]

Greetings,

I would like to place a command button on a worksheet that does the
following:

Save the file to the DESKTOP and at the same time append (the then
current) TODAYS DATE to the filename.

I suspect this is fairly straightforward, but I am new to this, and
would appreciate any guidance.

Regards,

Todd D. Levy

 

[Jacques Brun]

Todd,

I recorded and customized the following macro. I
tested it and it works. You need to change the path
"C:\WINNT\Profiles\jbrun\Desktop\" to the right one for
your environment, and to associate the macro to a button.
The appended date has the form mm-dd-yy. You can change it
to whatever other format you want (e.g. dd-mmm-yyyy)
provided the format has no slash or dot, which would
create uncorrect filenames.

regards
Jacques

===== Macro code here after ========================

Option Explicit

Sub saveWdate()

Dim newnm As String

newnm = ActiveWorkbook.Name
newnm = Left(newnm, Len(newnm) - 4) & _
Format(Date, "mm-dd-yy") & ".xls"

ActiveWorkbook.SaveAs FileName:= _
"C:\WINNT\Profiles\jbrun\Desktop\" & _
newnm, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

 

[Todd D. Levy]

Jacques,

First, thank you for the code and your quick response. This has been
very helpful.

I have since realised that different versions of Microsoft's operating
systems have different paths to the desktop.

Rather than create different versions of this spreadsheet for each
version of the O/S (and then have to update multiple spreadsheets), I
would like to make a change.

Instead of saving the file to the desktop, I would like save it (with
the new file name including the date as indicated in the code below) to
the same location that the original (template) file is located.
Basically, the default behavior of Excel when a file is saved.

Can I do this by simply removing the path to the desktop in the code
below, or is it more complex than that?

Regards,

Todd