Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save it giving as a name the content of a cell
I wonder who could help me with this. I've got a spreadsheet called Blank
Form saved as a template. I want to attach a macro to a button in the spreadsheet called Save It in a way that when someone clicks on it the Save window opens up and in File name it writes the content of cell A1. The content of the cell will be something like NAT-NU-1-001 and it should ask where to save it but give the name of the file by default. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save it giving as a name the content of a cell
You need to first add a command button to worksheet. follow these directions
1) Open Control Toolbox toolbar: View Menu - Toolbars - Control Toolbox. Trianle on toolbar toggle between enter/exit Design Mode. Placing mouse over Triangle should say exit (which means you are in design mode). If it says Enter, then press button once. 2) Press Command Button, then click on worksheet to add button. Resize button if necessary. 3) Press Properties on Toolbar. Change Caption to "Save It". 4) Right Click Command button and select View Code. 5) Paste this code into VBA window between two lines that already exists. filesavename = Application.GetSaveAsFilename( _ InitialFileName:= _ "c:\temp\" & Range("A1") & ".xls", _ FileFilter:= _ "Excel Files (*.xls), *.xls") ThisWorkbook.SaveAs filesavename 6) Close VBA window 7) On toolbar, Press Triangle (exit design mode). 8) Button should now work. I wonder who could help me with this. I've got a spreadsheet called Blank Form saved as a template. I want to attach a macro to a button in the spreadsheet called Save It in a way that when someone clicks on it the Save window opens up and in File name it writes the content of cell A1. The content of the cell will be something like NAT-NU-1-001 and it should ask where to save it but give the name of the file by default. Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save it giving as a name the content of a cell
Thank you Joel. Your reply was very helpful. I wonder if you could help me
with this related requirement. I would like to insert another button that when clicked it sends the file as an attachment. Something similar to the File - Sent To - Mail Recipient (as attachment). The problem is that if the user didn't save the file previously to sending it as an attachement it gives to the attached file the name False.xls (because I am working with a template). What would be the code that Sends it as an attachment and if the file hasn't been given a name yet, it assigns it the name as described in the code that you gave me in your answer (name from cell A1). I would really appreciate if you could help me with this. Thank you, "Joel" wrote: You need to first add a command button to worksheet. follow these directions 1) Open Control Toolbox toolbar: View Menu - Toolbars - Control Toolbox. Trianle on toolbar toggle between enter/exit Design Mode. Placing mouse over Triangle should say exit (which means you are in design mode). If it says Enter, then press button once. 2) Press Command Button, then click on worksheet to add button. Resize button if necessary. 3) Press Properties on Toolbar. Change Caption to "Save It". 4) Right Click Command button and select View Code. 5) Paste this code into VBA window between two lines that already exists. filesavename = Application.GetSaveAsFilename( _ InitialFileName:= _ "c:\temp\" & Range("A1") & ".xls", _ FileFilter:= _ "Excel Files (*.xls), *.xls") ThisWorkbook.SaveAs filesavename 6) Close VBA window 7) On toolbar, Press Triangle (exit design mode). 8) Button should now work. I wonder who could help me with this. I've got a spreadsheet called Blank Form saved as a template. I want to attach a macro to a button in the spreadsheet called Save It in a way that when someone clicks on it the Save window opens up and in File name it writes the content of cell A1. The content of the cell will be something like NAT-NU-1-001 and it should ask where to save it but give the name of the file by default. Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save it giving as a name the content of a cell
I don't think you can do exactly what you want to do. You can't rename a
spreadsheet. You can only saveas the spreadsheet to change the name. You could save the file in a temporary directory if you don't want to over-write an existing file. I don't know if your filename includes a path so I inluded two versions of the code. One version strips the pathname the other doesn't. An unsaved file doesn't contain the extension.xls. So if you get the thisworkbook.name and it doesn't contain xls, then it is unsaved. Sub test() If InStr(ThisWorkbook.Name, "xls") 0 Then 'code if saved Else 'code if not saved ThisWorkbook.Name = Range("A1").Text End If End Sub Sub test() If InStr(ThisWorkbook.Name, "xls") 0 Then 'code if saved Else 'code if not saved NewName = Range("A1").Text Do While InStr(NewName, "\") 0 NewName = Mid(NewName, _ InStr(NewName, "\") + 1) Loop ThisWorkbook.SaveAs _ Filename:="c:\temp\" & NewName End If End Sub "Montse" wrote: Thank you Joel. Your reply was very helpful. I wonder if you could help me with this related requirement. I would like to insert another button that when clicked it sends the file as an attachment. Something similar to the File - Sent To - Mail Recipient (as attachment). The problem is that if the user didn't save the file previously to sending it as an attachement it gives to the attached file the name False.xls (because I am working with a template). What would be the code that Sends it as an attachment and if the file hasn't been given a name yet, it assigns it the name as described in the code that you gave me in your answer (name from cell A1). I would really appreciate if you could help me with this. Thank you, "Joel" wrote: You need to first add a command button to worksheet. follow these directions 1) Open Control Toolbox toolbar: View Menu - Toolbars - Control Toolbox. Trianle on toolbar toggle between enter/exit Design Mode. Placing mouse over Triangle should say exit (which means you are in design mode). If it says Enter, then press button once. 2) Press Command Button, then click on worksheet to add button. Resize button if necessary. 3) Press Properties on Toolbar. Change Caption to "Save It". 4) Right Click Command button and select View Code. 5) Paste this code into VBA window between two lines that already exists. filesavename = Application.GetSaveAsFilename( _ InitialFileName:= _ "c:\temp\" & Range("A1") & ".xls", _ FileFilter:= _ "Excel Files (*.xls), *.xls") ThisWorkbook.SaveAs filesavename 6) Close VBA window 7) On toolbar, Press Triangle (exit design mode). 8) Button should now work. I wonder who could help me with this. I've got a spreadsheet called Blank Form saved as a template. I want to attach a macro to a button in the spreadsheet called Save It in a way that when someone clicks on it the Save window opens up and in File name it writes the content of cell A1. The content of the cell will be something like NAT-NU-1-001 and it should ask where to save it but give the name of the file by default. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I restore previous content before a save? | Excel Discussion (Misc queries) | |||
Requiring Cell Content on Save | Excel Discussion (Misc queries) | |||
File Save Macro as Cell content | Excel Programming | |||
save workbook as the date content of a cell within a sheet | Excel Programming | |||
Save as using cell content in file name | Excel Programming |