Command Button Question
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
"mathew" wrote in message
...
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.
3. is ist possible to have a date and time format in the code?
Thanks so much!
"JulieD" wrote:
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
"Todd D. Levy" wrote in message
.net...
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
|