Command Button Question
 

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

Command Button Question
 

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

Command Button Question
 

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

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

Command Button Question
 

Hi Mathew

the activeworkbook.name returns the name of the current workbook. what i'm
doing is stripping out the "datetime" information at the end of it and
the.xls bit so that i can compare it with what's in I3

the two functions used are the left function which returns a given number of
characters from a string
=left(string,number of characters to return)
and len which determines the length of a string
=len(string)
here i'm taking the current name and removing the last 17 characters from
it.

Hope this makes sense
cheers
JulieD


"mathew" wrote in message
...
Julie: Thank you very much! 1 question, what does this line do:
fname = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 17)


"JulieD" wrote:

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