Excel Templates

The purpose of an Excel template is to provide a starting place for a new workbook, worksheet, chart, or code module. Creating a template is easy. We simply create a new workbook and save it as a template using the Save As command.

For instance, suppose we start a new workbook and enter the number 123 in the first cell of the first sheet. Then we save the workbook in the templates directory (more on this later) as a template called test.xlt. When we next invoke the New command from the File menu, Excel will display a

New dialog with an icon for our template, as shown in Figure 10-4. When we select the test.xlt icon, Excel will create a new workbook and copy the data from the template into the workbook.

Figure 10-4. The New dialog showing template icons

FTMad mivipfh

SelKtiTl Iran t spft rt rrk> f:x.


It is very important to note that the data (and other things such as formatting) as well as macros are actually copied to the workbook, after which all connection between the template and the new workbook is severed. This is quite different from the way that Microsoft Word uses templates. A Word template remains attached to the document. Certain changes, such as the addition of styles or macros, can be saved either in the template or in the document itself, but Word never copies macros from a template into a document. Also, several templates can be opened at one time (the so-called global templates), each of which may affect the document. Word templates are dynamic; Excel templates are static.

This reduces the usefulness of Excel templates considerably, for if we create a template that contains lots of code, for instance, then each workbook that is based on that template will contain its own copy of that code. This can be a major waste of space and can also make it very difficult to maintain and upgrade the code. For these reasons, I generally avoid using Excel templates whenever possible.

For the record, however, we should note that the following items are transferred to a new workbook or worksheet that is based on a template:

• The number and type of sheets in a workbook

• Cell and sheet formats set using the Format menu

• Page formats and print-area settings for each sheet

• Cell contents

• Worksheet graphics

• Custom toolbars, macros, hyperlinks, and ActiveX controls on forms; custom toolbars must be attached to the template

• Protected and hidden areas of the workbook

• Workbook calculation options and window display options set using the Options command on the Tools menu

We should also note that Excel supports several types of special templates called autotemplates. They are templates with the following names:

• Macro.xlt (for Excel version 4 macros)

Figure 10-4. The New dialog showing template icons

When the Book.xlt template is stored in the XlStart subdirectory, Excel bases all new workbooks on this template when you select the Workbook icon in the New dialog (see Figure 10-2).

If you want new worksheets to have a special format, then you can create a template named Sheet.xlt and place it in the XlStart folder. Then every time the Insert Worksheet menu item is invoked, Excel will make copies of all of the worksheets in the Sheet.xlt template and place them in the current workbook. Note that this can be more than one sheet if there is more than one sheet in Sheet.xlt.

By now you get the idea. The other autotemplates work similarly.

It is also important to know that all of the Office applications use the same default directory for templates. Hence, this directory may contain Word, Excel, PowerPoint, and Access templates. But Word is the only Office application (as of Office 97) that provides a way for the user to change this directory (from the File Locations tab of the Options dialog under the Word Tools menu). It follows that, changing this directory using Word will change it for all Office applications!

+1 0

Post a comment