Template - Save As File Name

[Jani]

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]

Instead of using the builtin menus (File|Save, File|SaveAs), have the associate
run your Save_It macro.

 

[Jani]

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]

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]

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]

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)

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?

 

[Jani]

Dave - Thanks for your quick responses & excellent help. I will check out the
two sites you mentioned. jms