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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com