What's the point of a Template?

[stoolpidgeon]

1. What's the point of making my file a Template (saving as a .xltm)? What special priviledges does this afford me?
As far as I can see it's exactly the same as saving as a .xlsm?

2. I would like to create a file that a user can edit but cannot save over - when they choose to Save the file it should prompt them to Save As.

Edit: Ignore (2) - after closing and re-opening the file ctrl+s now prompts with Save As dialogue box.
I'd be interested to know what other benefits are afforded me so answers to (1) still welcome.

 

[theBardd]

xlsm files would be the container of your code. Templates would be pro-forma outputs with all of the branding, formulae and so on that you need for your output, but without the variables. Your code would open the template, populate all of the variable fields and then save as a new working document.

Think about invoices as an example.

 

[Rick Rothstein]

1. What's the point of making my file a Template (saving as a .xltm)? What special priviledges does this afford me?
As far as I can see it's exactly the same as saving as a .xlsm?

If you save a file as a Template (macro enabled or not), then that file can be used as a starting point for one or more other projects. You would choose it when you selected New to create a new workbook. For example, let's say there is a certain set of functions that you want available for some or all of the new workbooks you create from now on. Put the functions in a Module in Blank workbook and then save the workbook as a macro enabled Template. Now, close Excel and then reopen it... create a New workbook, but instead of selecting a Blank workbook, select the Template with the name you gave your Template file when you saved it above. Now open the VB editor and look in the Project Window... you will see a Module already listed there... open it and you will see the functions you included in the file when you saved it as a Template. Now my example was for VB code, but you can set up the worksheet as well. For example, you could make Column A wider and format Column B as Date, and give Column C a custom format and fill the interior of Column D with a color, and so on, and so on. Once you have set it up the way you want, save it with a name that means something for why you set it up the way you did. Then, when you start a New workbook, select that Template and all the formatting you did will automatically be in place.

 

[cytop]

How about if you designed an invoice template...? Saves recreating all the formatting/formulas every time you want to raise an invoice.

 

[stoolpidgeon]

I understand the benefit of setting up a workbook with formatting to save you recreating the same formatting again, but I wasn't sure why this couldn't be achieved with just saving it as a standard workbook rather than as a template.

What I really want to know is the technical benefits of a template, such as that you can't save over it (it prompts you to Save As) and that you can see a list of Templates when selecing to create a New workbook.

 

[theBardd]

Templates fit perfectly into the design paradigm where you keep components, code, data, presentation, in separate layers/workbooks, and so help provide a more resilient, more robust solution.

 

[Rick Rothstein]

What I really want to know is the technical benefits of a template, such as that you can't save over it (it prompts you to Save As)

That is probably the biggest benefit.

...and that you can see a list of Templates when selecing to create a New workbook.

And that makes them convenient to use.