Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Question
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button add another command | Excel Discussion (Misc queries) | |||
2 part question - macro / command button | Excel Discussion (Misc queries) | |||
Command Button | Excel Programming | |||
Command Button vs Control Button | Excel Programming | |||
Command Button vs Form Button | Excel Programming |