Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
In looking at all these helpful hints, I am doing something wrong with what
should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" ..SaveAs fname End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
Instead of using the builtin menus (File|Save, File|SaveAs), have the associate
run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
Wow - thanks for the quick reply!! So it works but not quite right. It saves
it but on the Desktop since that is where I put it and the associate might want to place it elsewhere and the macro needs to run automatically when the file is closed. Do you have further instructions for me? "Dave Peterson" wrote: Instead of using the builtin menus (File|Save, File|SaveAs), have the associate run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
You can specify the location in your code:
..SaveAs "C:\myfolder\myfolder\" & fname But if you make this automatic when the file is closed, then won't it run each time the file is closed? And what happens if the user wants to save to a different location--or even close without saving???? Jani wrote: Wow - thanks for the quick reply!! So it works but not quite right. It saves it but on the Desktop since that is where I put it and the associate might want to place it elsewhere and the macro needs to run automatically when the file is closed. Do you have further instructions for me? "Dave Peterson" wrote: Instead of using the builtin menus (File|Save, File|SaveAs), have the associate run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
You are so right. So what would be ideal in my world is if the macro would
automatically pick up the text in a cell and then the associate would be able to browse to where the file should be saved. Is that possible? "Dave Peterson" wrote: You can specify the location in your code: ..SaveAs "C:\myfolder\myfolder\" & fname But if you make this automatic when the file is closed, then won't it run each time the file is closed? And what happens if the user wants to save to a different location--or even close without saving???? Jani wrote: Wow - thanks for the quick reply!! So it works but not quite right. It saves it but on the Desktop since that is where I put it and the associate might want to place it elsewhere and the macro needs to run automatically when the file is closed. Do you have further instructions for me? "Dave Peterson" wrote: Instead of using the builtin menus (File|Save, File|SaveAs), have the associate run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Jani wrote: You are so right. So what would be ideal in my world is if the macro would automatically pick up the text in a cell and then the associate would be able to browse to where the file should be saved. Is that possible? "Dave Peterson" wrote: You can specify the location in your code: ..SaveAs "C:\myfolder\myfolder\" & fname But if you make this automatic when the file is closed, then won't it run each time the file is closed? And what happens if the user wants to save to a different location--or even close without saving???? Jani wrote: Wow - thanks for the quick reply!! So it works but not quite right. It saves it but on the Desktop since that is where I put it and the associate might want to place it elsewhere and the macro needs to run automatically when the file is closed. Do you have further instructions for me? "Dave Peterson" wrote: Instead of using the builtin menus (File|Save, File|SaveAs), have the associate run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template - Save As File Name
Dave - Thanks for your quick responses & excellent help. I will check out the
two sites you mentioned. jms "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Jani wrote: You are so right. So what would be ideal in my world is if the macro would automatically pick up the text in a cell and then the associate would be able to browse to where the file should be saved. Is that possible? "Dave Peterson" wrote: You can specify the location in your code: ..SaveAs "C:\myfolder\myfolder\" & fname But if you make this automatic when the file is closed, then won't it run each time the file is closed? And what happens if the user wants to save to a different location--or even close without saving???? Jani wrote: Wow - thanks for the quick reply!! So it works but not quite right. It saves it but on the Desktop since that is where I put it and the associate might want to place it elsewhere and the macro needs to run automatically when the file is closed. Do you have further instructions for me? "Dave Peterson" wrote: Instead of using the builtin menus (File|Save, File|SaveAs), have the associate run your Save_It macro. Jani wrote: In looking at all these helpful hints, I am doing something wrong with what should be simple code. I have an Excel template and want the file to be saved to a reference cell. When I click on Save, Close, Save as.. it defaults to the name of the template. The associate can save the file wherever she/he wants to. Need some assistance - thanks! Jani Sub save_it() Dim fname With ActiveWorkbook fname = .Worksheets("worksheet1").Range("b9").Value & ".xls" .SaveAs fname End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a .XLTM template file default to save as .XLSM type? | Excel Discussion (Misc queries) | |||
When you hit Save on a template, how can you save as worksheet? | Excel Worksheet Functions | |||
How can I save a file as a template? | Excel Discussion (Misc queries) | |||
Excel marcos firing on file save as but not file save | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming |