Saving a Workbook as the contents of a cell

I have read 5+ threads trying to figure out how to do this, and while I've gotten close, something has always been a problem for my needs.

Here's what I'm trying to do: [Excel 2007]
- I have created a template for a tracking sheet for my small business.
- When the template is used, cell D3 will be typed in.
- I want the whole workbook [as there are 6 sheets in the workbook] to be saved as the contents of cell D3 on the first sheet [Sheet 1's name is "Tracking"] onto my server.

Problems:
- While I'm computer-savvy, I've never messed with Excel macros before today.
- The template needs to have the macro built into it so that when the template is opened as a regular workbook, the macro opens with it and is ready to go.
- It has to be automatic when you click "Save As" without having to run the macro separately.

If anyone can help me, I couldn't thank you enough. My head is spinning.

The trick is to make sure you save and use your workbook as an actual template, saved in Excel template format.(*.xltm). The benefit here is that each time you activate the template, it is opened without a name, thus SAVEAS is your only option, very nice. Even Ctrl-S will activate the SaveAs interface.

Then a BeforeSaveAs workbook macro does the rest for you.

1) Right-click the Excel icon to the left of the File menu and select VIEW CODE, this should open you directly into the ThisWorkbook code module.

If that doesn't work, the press Alt-F11 to open the VBEditor and open the ThisWorkbook module found in the VBAProject window

2) Paste in this code and edit the fPath variable to your server location desired:

Please Login or Register  to view this content.

3) Close the editor and save as a macro-enabled TEMPLATE in your templates folder (*.xltm)

4) To use the template, use File > New and select the template from your local templates, or create a handy shortcut to the template itself

5) Just press CTRL-S to save it, or use SAVEAS...either way you're workbook will be saved the way you want. If D3 isn't filled in, then you will be warned.

The "beep" will confirm audibly the file was saved.

Here comes the problem I'm facing with that.

The template file that I have ["Tracking Sheet Template.xltm"] won't let me save it as an .xltm after I enable the macro, the macro only saves it as an .xlsx.

In addition, it's popping up an error message that says this after I enter in the code you gave me:
"The following features cannot be saved in macro-free workbooks: VB project.
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click yes."

The thing is, the template I'm running is macro-enabled [.xltm] with macros enabled under macro security.

Also, it would work for my purposes if when you clicked "Save As" and the Save As Prompt with the file path and folder pops up, if it just "suggested" the contents of D3, as opposed to A1 or "Tracking Sheet Template1". I don't honestly know whether or not that's even possible though.

P.S. Thank you so much in advance for your help, even if this doesn't end up working.

1) Turn on the Control Toolbox toolbar
2) Click on the Design Mode icon to temporarily turn off macros
3) Now you can save your sheet as an xltm file
4) Close the workbook and activate it using the template file, it should work as originally noted

You can change this line and it will save the file as a macro-enabled workbook, not a template. Only you can save the template as a template by turning off the macro and saving to a template file to replace your existing file.

Please Login or Register  to view this content.

Better yet, try this alternate:

Please Login or Register  to view this content.

Actually: One little problem.

It's saving it as a non-macro enabled workbook.
I couldn't find the line to change like you said in post 5.

So as to avoid a triple post:

EDIT:
After I put in the code you gave me in post 6, it was working well. However, I didn't actually click save until I tried to run the entire template.
That was when it told me it was trying to save it as a non-macro enabled workbook even though the "file type" was saying it was macro-enabled.
I went back to the code you gave me in post 4, put that in and substituted in the code you gave me in line 5 and tried that, and it works.
The biggest difference is that the code you gave me in post 6 allows the user to choose to type in a different name and choose a different file location if they choose.
I like that option, but couldn't work the code so that it would save as macro-enabled.

Code #6 is specifically saving as a macro-enabled file type only (*.xlsm). Even when I try to make it non-macro-enabled on purpose it doesn't work, it's always macro-enabled. Your results are thus not understood. Maybe if you close Excel completely, open fresh and try the original code 6 as is again.

Care to post up this misbehaving workbook so I can observe this behavior?

Were you able to see the template?

I'll look at it shortly, I'm peeking in periodically from work.

No VBA in that file anywhere.

I inserted the code #6 with no edits into ThisWorkbook module, disabled macros and saved as a template.

I closed everything, then double-clicked the template file. It opened with no extension, meaning a SaveAs is being forced for first save.

I pressed CTRL-S and the file selector window appeared with the default suggest value from D3.

I clicked OK and the file saved as a .xlsm file.

I closed the file, and reopened the saved doc, the macro was there and working.

I attached four pictures showing my difficulties.

I followed your steps exactly, another time.

While I do believe that your code works perfectly, there's something wrong on my end that's affecting it.

Perhaps it's that I'm using Excel 2007? I'm not sure.

If you think you know what the problem is, I thank you, and if not, thank you so much for the help you have given me, and I will definitely put it to use.

RT Electric, LLC
Major

EDIT:
TST1 shows it working as expected.
TST2 shows the error message I get after I try to click Save in TST1.
TST3 shows the message after I click No in TST2.
TST4 is what is shown to me after clicking Debug in TST3.

EDIT 2:
I opened the TestTemplate.zip you attached, and it gave me precisely what I get in TST1/2/3/4.
I can only assume it's an XL 2007 issue or something very obscure in my settings.

What about this:

Please Login or Register  to view this content.

At some point you're going to edit the fPath to something specific to your system, yes?