8.5 Templates

Templates

Excel s templates offer a way to provide standard formatting and styles for documents. If you ve been reading this book sequentially, you ve already read about Word s templates in Chapter 5, "Intermediate Word," and how they let you specify common elements like the page setup, headers and footers, and even boilerplate text so that every new document contains the same things. Excel offers the same deal.

Every spreadsheet you create in Excel is based on a template a file with an extension of XLT. If you don t specify which template to use, the default template is used. By default, this is an empty document containing a standard set of styles. It s installed when Excel is installed, along with a number of other templates. To see the available templates, choose File|New from the menu, just as you do in Word.

What goes into a template?

A template can contain anything that s in a spreadsheet, from the simplest spreadsheet to a complex analysis report that includes many formulae, PivotTables, and charts just waiting for a few pertinent data items to be added. Templates can also contain macros, including one that runs automatically when you create a worksheet based on the template. Just like templates in Word, Excel templates can be as much or as little as you choose to make of them.

Finding templates

Templates are stored in several different places. The templates installed by Excel 2000 are put into a Templates subdirectory of the Office installation, and then they go down one level into a subdirectory named with the numeric code for the language you re using (1033 for American English). In addition, each user can set a user template directory and a workgroup template directory.

Detailed information about how Excel stores user templates is contained in the "Finding templates" section in Chapter 5. While it is a Word chapter, this is something that Office has standardized.

One thing that is slightly different is how to find the templates path. Two properties are available from the Application object: TemplatesPath and NetworkTemplatesPath. TemplatesPath is the path to your local machine s templates, and the NetworkTemplatesPath lets you point to another set of templates on the network.

Using templates

You can use templates in several ways when automating Excel. The simplest is to create new documents based on existing templates. To do so, specify a template, including the path, as the first parameter of the Workbooks.Add method. No templates are installed with the default installation of Office/Excel, so don t expect this template name to work:

oBook = oExcel.Workbooks.Add( oExcel.TemplatesPath + "MyTemplate.XLT" )

Once you create a new workbook based on a template, you can treat that workbook just like any other new workbook. However, you have the advantage that it contains whatever special text, formatting, and styles were stored in the template.

Creating templates

You can also create templates with Automation. Any workbook can be saved as a template by passing the appropriate parameter to the SaveAs method. To create a new template, create a workbook, format it as desired, create any styles, charts, PivotTables, or other features you want the template to have, and then call SaveAs like this:

#DEFINE xlTemplate 17

oBook.SaveAs(oExcel.TemplatesPath + "MyNewTemplate.XLT", xlTemplate)

As in interactive Excel, you can store the template in a subdirectory to have it appear on a different page in the File|New dialog. Of course, if you re working with it through Automation, you don t really care where it appears. In fact, with Automation, it doesn t matter where you store templates because you can specify where Excel should look for them. However, keeping them together with other templates means that interactive users can find them, as well.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved



Microsoft Office Automation with Visual FoxPro
Microsoft Office Automation with Visual FoxPro
ISBN: 0965509303
EAN: 2147483647
Year: 2000
Pages: 128

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net