Save file as value of a cell

[cm3pyro]

Hello Excel Forum

I am working on a VBA code that does the following: any help or suggestions would be greatly appreciated. The idea is that the user fills out a template with information and when they are ready to save the file it saves the file with a new file name and closes the template without saving or changing the template in any way.

On button press open the Save As window in excel
In the Save As window where the file name is displayed I would like for it to display a file name taken from a value in a cell from a different worksheet.
When the user presses save in the Save As window I would like the template document to close without saving and the document that was just saved to stay open with the new file name.

Thank you

[JBeaucaire]

When a true Excel Template is opened, you are in no danger of overwriting it because template files (when opened properly) open in a SaveAs only state.

Save your excel document as an .XLT file so that it is a true template. Open it with a shortcut to it placed somewhere, or the FILE > NEW > Template option.

Now SaveAs is your only option and your template is fine.

==========

To get a default filename in the SaveAs dialog, put this macro into the ThisWorkbook module of your template:

Please Login or Register  to view this content.

[cm3pyro]

JBeaucaire

Thank you! This works great.

I have a few questions though.

Is it possible to get the file name from a different excel document instead of from a sheet in the template document?
Is it possible to have the Save As window navigate to a directory to save the file in?
Example: F:\Engineering\

Thanks

[JBeaucaire]

Is it possible to get the file name from a different excel document instead of from a sheet in the template document?

Please be very specific: Filepath, filename, sheetname, cell address

Is it possible to have the Save As window navigate to a directory to save the file in?
Example: F:\Engineering\

Yes, I'll show both answers together.

[cm3pyro]

Filepath: F:\Engineering\TEST RESULTS\PROCEDURES\REPORT TEMPLATES
filename: Test Report Number Generator.xls
sheetname: Report
cell address: B15

[cm3pyro]

Still around JBeaucaire?

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[cm3pyro]

JBeaucaire

I am still having some issues. The SaveAs window is not opening into F:\Engineering

[JBeaucaire]

I just tried the macro as posted again and it opens to the path listed.

[cm3pyro]

I just tried the macro as posted again and it opens to the path listed.

I am still having issues resolving this problem. What would the procedures be to add this macro to a button on my report template so i can test it out that way?

Thank you

[cm3pyro]

i think i have discorded the problem! F:\ is a networked drive. when i change the code to navigate to a folder located on my C drive it works perfectly.

And, is there any way to remove the quotes around the file name in the SaveAs window?

[cm3pyro]

I figured it out, i added ChDrive "F:\" before the ChDir

Now, is there any way to remove the " " from the file name in the SaveAs window?

[JBeaucaire]

You do seem to get odd results. I don't get quotes around my strings in the Dialog box. Any chance the text strings in the cells being looked up have quotes around them?

Maybe this, change this one line of code:

Please Login or Register  to view this content.

[cm3pyro]

This is the formula in the cell i am pulling the value from for the file name.

Please Login or Register  to view this content.

[cm3pyro]

Please Login or Register  to view this content.

didnt work

[JBeaucaire]

Here you go:

Please Login or Register  to view this content.

[cm3pyro]

JBeaucaire

I am trying to put all of this code to use now and i am running into a pretty substantial problem. Everything works great but after the template file is filled out and automatically saved as a .xls when i need to edit that file it does not let me save the updated information.

Is there a way to clear all of the VBA code once the template is filled out and saved as a .xls ..so there is no VBA code saved in the .xls document?

Thanks

[JBeaucaire]

I don't know why it won't let you, the code only invokes during a SAVEAS function. Any regular SAVE function should result in the file simply saving normally.

[cm3pyro]

Please Login or Register  to view this content.

Worked perfectly, thank you!

Let my attempt to rephrase the issue i am having with saving the files created from the templates.

After i use the template to create the file and save it, it is given a specific file name from the value in my test report number generator worksheet. This number in the file name changes for every test report. When i go back to update a test report made from the template, and when i click save the Save dialog box appears with the most recent file name generated by the file name generator..it does not simply just update the file when the saved button is clicked.

The VBA code used in the template file is not necessary in the .xls workbook it creates, that's why i was wondering if it is possible to delete the vba code in the .xls document when it saved from the template for the first time.

[JBeaucaire]

I understand the situation, it just doesn't make sense to me.

The code does an IF SaveAsUI test at the beginning of the code. The only time it should do the "offer a new filename" trick is when you are doing a SAVEAS, not when you just do a normal save.

When it's a normal save, it should just save.

Upload one of the resulting files and let me look at it.

[cm3pyro]

template: click here
resulting file from template: click here

[JBeaucaire]

Every time I press CTRL-S to save the A.xls file, it just saves. No popup.

You could add this near top just after the DIMs, it should have been there all along:

Please Login or Register  to view this content.

...and this as well:

Please Login or Register  to view this content.

As I said, when not doing a SaveAS, it should just save, and for me it does.

[cm3pyro]

That fixed it! Here's the completed code. Thank you so much, you efforts are greatly appreciated.

Please Login or Register  to view this content.

[whatsmyname]

Just subscribing to this post for future education and reference.

Thank you.

[JBeaucaire]

CM3, you can remove those same two lines you added at the top from inside the IF statement, they are unnecessarily duplicated there, sorry I didn't point that out earlier.

==========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.