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

 

[JulieD]

Hi Todd

this code should give you what you need
*********
Sub savetodesktop()
Dim pname As String
Dim fname As String
pname = "C:\Windows\Desktop\"
fname = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
fname = fname & Format(Now, "ddmmyyyy") & ".xls"
ActiveWorkbook.SaveAs Filename:="" & pname & fname & ""
End Sub
*********
however, depending on what version of windows you're running & how its all
setup this "desktop" might not be the desktop you're seeing as your desktop
so you might need to change the path to something like
"C:\Documents and Settings\All Users\Desktop\"
or instead of All Users your login name
- to get the button you can use either the command button off the control
toolbar or one of the drawing objects (under autoshapes on the drawing
toolbar) .. either way, copy & paste the above code into a module in your
workbook, create the drawing object on the sheet, right mouse click on it
and choose assign macro and choose the savetodesktop macro.

let us know how you go

Cheers
JulieD

 

[Guest]

Wouldn't that depend on the OS you're using? For example in XP wouldn't it be
C:/Documents and Settings/ then the user name then /Desktop
(e-mail address removed)

 

[JulieD]

Hi Mathew

okay
not sure what you mean however by #3

3. is ist possible to have a date and time format in the code?

*********
Sub savetodesktop()
Dim pname As String
Dim fname As String
pname = "C:\Windows\Desktop\"
fname = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 17)
If Len(Range("I3")) < 1 Then
MsgBox "There's nothing in cell I3 - Please try again!", vbOKOnly,
"Can't Save"
Exit Sub
Else
If fname = Range("I3") Then
fname = fname & Format(Now, "ddmmyyyy_hhmm") & ".xls"
Else
fname = Range("I3") & Format(Now, "ddmmyyyy_hhmm") & ".xls"
End If
End If
ActiveWorkbook.SaveAs Filename:="" & pname & fname & ""
End Sub

*********
Cheers
JulieD

Juile: Can you help me with these issues:
1. Your code will add the date on every time you hit the button, so you

will have 5 dates behind the file name if the user hits the button 5 times.
Is there a way to just have the current date?

2. I would also like to name the file for a name entered into a cell, say

I3. I want the button to name the file with the name in the cell and then
add the date proir to saving the file.