Working with Template Files | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

A template is essentially a model that serves as the basis for something else. An Excel template is a workbook that's used to create other workbooks. You can save any workbook as a template file (XLTX extension). Doing so is useful if you tend to create similar files on a regular basis. For example, you might need to generate a monthly sales report. You can save some time by creating a template that holds the necessary formulas and charts for your report. When you start new files based on the template, you need only plug in the values.

Viewing templates

Excel 2007 gives you access to many templates. To explore the Excel templates, choose Office New to display the New Workbook dialog box. The template categories appear as tabs in the New Workbook dialog box. In addition, the right side of the dialog box displays a list of templates that you've used recently.

The Microsoft Office Online section contains a number of categories. Click a category, and you'll see the available templates. To use a template, select it and click Download. Figure 4-3 shows some of templates available in the Invoices category.

Figure 4-3: Templates that you can use for invoices.

Microsoft Office Online has a wide variety of templates, and some are better than others. If you download a few duds, don't give up. Even though a template may not be perfect, you can often modify a template to meet your needs. Modifying an existing template is often easier than creating a workbook from scratch.

Note	The location of the Templates folder varies, depending on the version of Excel. To find the location of your Templates folder, execute the following VBA statement: MsgBox Application.TemplatesPath

Creating templates

Excel supports three types of templates:

The default workbook template: Used as the basis for new workbooks. This file is named book.xltx .
The default worksheet template: Used as the basis for new worksheets that are inserted into a workbook. This file is named sheet.xltx .
Custom workbook templates: Usually, these are ready-to-run workbooks that include formulas, but they can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get immediate results.

USING THE WORKBOOK TEMPLATE TO CHANGE WORKBOOK DEFAULTS

Every new workbook that you create starts out with some default settings. For example, the workbook has three worksheets, the worksheets have gridlines, text appears in Calibri 11-point font, columns are 8.43 units wide, and so on. If you're not happy with any of the default workbook settings, you can change them.

Making changes to Excel's default workbook is fairly easy to do, and it can save you lots of time in the long run. Here's how you change Excel's workbook defaults:

Open a new workbook.
Add or delete sheets to give the workbook the number of worksheets that you want.
Make any other changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Options dialog box.

To change the default formatting for cells , choose Home Styles Cell Styles and then modify the settings for the Normal style. For example, you can change the default font, size , or number format.
When your workbook is set up to your liking, choose Office Save As.
In the Save As dialog box, select Template (*.xltx) from the box labeled Save As Type.
Enter book.xltx for the filename.
Save the file in your \XLStart folder ( not in your Templates folder).
Close the file.

Tip	The \XLStart folder may be located in either of these directories: C:\Documents and Settings\ <username> \Application Data\Microsoft\Excel\XLStart C:\Program Files\Microsoft Office\Office12\XLStart

To determine the location of \XLStart, execute this VBA statement:

 MsgBox Application.StartupPath

After you perform the preceding steps, the new default workbook that appears when Excel is started is based on the book.xltx workbook template. You can also press Ctrl+N to create a workbook based on this template. If you ever want to revert back to the standard default workbook, just delete the book.xltx file.

Note	If you choose File New, and select Blank Workbook from the New Workbook dialog box, the workbook will not be based on the `book.xltx` template. I don't know if this is a bug, or if it's by design.

USING THE WORKSHEET TEMPLATE TO CHANGE WORKSHEET DEFAULTS

When you insert a new worksheet into a workbook, Excel uses its built-in worksheet defaults for the worksheet. This includes items such as column width, row height, and so on. If you don't like the default settings for a new worksheet, you can change them by following these steps:

Start with a new workbook and delete all the sheets except one.
Make any changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Excel Options dialog box.
When your workbook is set up to your liking, select Office Save As.
In the Save As dialog box, select Template (*.xltx) from the Save As Type box.
Enter sheet.xltx for the filename.
Save the file in your \XLStart folder ( not in your Templates folder).
Close the file.
Close and restart Excel.

After performing this procedure, all new sheets that you insert by clicking the Insert Worksheet button (which is next to the last sheet tab) will be formatted like your sheet.xltx template. You can also press Shift+F11 to insert a new worksheet.

Creating workbook templates

The book.xltx and sheet.xltx templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets.

Why use a workbook template? The simple answer is that it saves you from repeating work. Assume that you create a monthly sales report that consists of your company's sales by region, plus several summary calculations and charts. You can create a template file that consists of everything except the input values. Then, when it's time to create your report, you can open a workbook based on the template, fill in the blanks, and be finished.

Note

You could, of course, just use the previous month's workbook and save it with a different name. This is prone to errors, however, because you easily can forget to use the Save As command and accidentally overwrite the previous month's file. Another option is to use the New From Existing icon in the New Workbook dialog box. This creates a new workbook from an existing one, but gives a different name to ensure that the old file is not overwritten.

When you create a workbook that is based on a template, the default workbook name is the template name with a number appended. For example, if you create a new workbook based on a template named Sales Report.xltx , the workbook's default name is Sales Report1.xlsx . The first time that you save a workbook that is created from a template, Excel displays its Save As dialog box so that you can give the template a new name if you want to.

A custom template is essentially a normal workbook, and it can use any Excel feature, such as charts, formulas, and macros. Usually, a template is set up so that the user can enter values and get immediate results. In other words, most templates include everything but the data, which is entered by the user.

Note	If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an XLTM extension.