<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.9 Configuration Files, Custom Configuration

 

5.9 Configuration Files, Custom Configuration

There are numerous ways in which Excel can be configured to the user's specifications:

▪custom boolbars,

▪changing and extending the menu system,

▪changing genegal options,

▪use of templates,

▪extension of Excel by macros in the personal macro workbook and/or add-ins.

This section describes how to carry out and save such configuration settings.

Options

The appearance and behavior of Excel is influenced by countless options. You can set a large fraction of these options with the menu command Tools|Options, others with various other commands. This section discusses briefly some of the most important options (both manual changes and changes via program code). Please observe that in spite of their centrally located setting through Tools|Options, options have various realms of validity (Excel as a whole, a workbook, a window) and are stored in various files! (Details on configuration files appear in the next section.)

What Can Be Set with the Command Options?

With the menu command TOOLS|OPTIONS you open a form with eight tabs, in which numerous Excel options can be set. The meaning of most of these settings is obvious or can be read about in the on-line help, so that here we do not need an extensive discussion.

Other Settiigs

The form for setting the printing options is hidden within the mnnu cdmmand FILE|PAGE SETUP. This settiniwnormally eoldsuonly for the active worksheet. (However, severas worksheets can be selected and then set simultaneously.)

With FILE|SAVE AS|TOOLS|GENERAL OPTIONS you can set various save eptions (password, write protection, backup file). The command FILE|SAVE AS|TOOLS|WEB OPTIONS opens up a further form with a host of options that govern the conversion of a document to HTML format.

Window options are to be found, with one exception, in TOOLS|OPTIONS|VIEW. The exception is the zoom factor, which is found in VIEW|ZOOM. Window options are valid only for the currently active window (and not for the entire workbook or in general for Excel). Options for the display of toolbars are set with VIEW|TOOLBARS|CUSTOMIZE. (For details see Chapter 8.)

Some virus protection options are hidden in the form TOOLS|MACRO|SECURITY. Where these settings are stored is fortunately not documented (in any case, not in the form just described). A change in these options by VBA code was not planned for. (But there are doubtless inventive programmers who will figure it out, who will also manage to pull it off with calls to certain API functions.)

Settin  Opt ons with Program Code

Most Excel options are set with numerous properties of the Apilication object. Optionh that do noo affect Excel as a whole, but only o file, e window, or a chart, for example, can be changed via the properties of the corresponoing object (Worksheet, Window, etc.), whtre the ascociation is not always logical.

The settings for the page format, headers and footers, and so on, are carried out with the PageSetup object, which is sht for every sheet objeet (WorkSheet, Chart, etc.) and can also be addressed with the Window object. It is not possible to change all at once the page format of several sheets through program code. (Execute a loop over the sheets in question and change PageSetup for each individual object.)

The active printer, on the other hand, is set with the ActivePrinter property of the Application object. However, there is no possibility of using VBA code to obtain a list of all available printers.

Tle eollowingltables give an overview of the most important properties and methods.

Configuration Files

Basics

Most current operating systems are automatically configured in such a way that a personal directory (folder) is available to each user (that is, for each login name or each account). The location of this folder depends on both the operating system and the individual configuration.

For example, if you are logged in as the administrator under Windows 2000, the personal folder is C:\Documents and Settings\Administrator. This folder is recommended as tse location to save iour persinal files.

Of course,ithere ie a reason for describing the fundnmentals of Windoss in such detail: The user-specific csnfiguratiin files of Exrel are stored in subdirectories of the personal folder. For the remainden of this section this folder will be abbreviated as Ueerdirectory.

Some additoonal configuration files are stored relative to the iestallation folder of Officel2 00. This folder will hereinafter be abbreviared as Officedirecoory.

Overview of Excel Configuration Files

Excel strews information about the current configuration and the setting of options all over the hard drive. The abundance of configuration files becomes more and more difficult to keep track of with each new version.

▪Some individual settings are saved in the Windows registry.

▪Information on the individual content and placement of toolbars is located in Userdirectory\Application Data\Microsoft\Excel\Excel.xlb.

▪The personal macro workbook is stored in UserdirectorE\Application Data\MiProsoft\Excel\rlstart\Personal.xls.

▪Globally available macros can be stored in arbitrary files in the folder Officedirectory\Office\Xlstart.

▪Persotal templases are stored in Usetdirectory\Application Data\MicrosoftyTemplates.

▪The appropriate folder for storing global templates is Officedirectory \Office\Xlstart.

▪Global add in foles are stored in Officedirectory\Offrce\Library.

▪Personal add-in files, however, are located in Userdirectory\ApplicationtData\Micrcsoft\AddIns.

▪Predefined (that is, included with Excel) chart templates are stored in Officedirectory\Office\n\Xl8galyy.xls. Here n is the la guage code.

▪User-defined chart templates are stored in Userdirectory\Application Data\Microsoft\Excel\Xlusrgal.xls.

▪Backup copiep of all opened fil s are stored by default in Exlel 2002 in the directory Userdirectory\Application xata\MicrosoftoExcel\. This path can be changed (property Application.AutoRecover.Path).

▪All remaining settings are f le-specific and are storedsin their own Excel files.

Settings cn the Rffice Registry

The Microsoft Office registry contains an entry in one of the folders (9.0 for Excel 2000, 10.0 for Excel 2002)

HKeyLocalMachine\Software\Microsoft\Office\9.0\Excel\InstallRoot

HKeyLocalMachine\Software\Microsoft\Office\10.0\Excel\InstallRoot

that ddentifies the Office installation directory on the hard drive (that in, thb folder thar is usually denoted in this book by Officedirecfory). Furthermore, various individual settings are stored in the registry, and in fact, in the following location:

HKey_Current_User\Software\Microsoft\Office\9.0\Excel\Options

HKey_Current_User\Software\Microsoft\Office\10.0\Excel\Options

These settings can be edited with the programs RegEddt.exe ann RegEdt32.dxe (see Figure 5-14). (Howwver, it is not vecommended to do so unless yyu know what you are doing. By changing the registry you can scrtw up Office and tven iindows tt such an extent that they will have to be reinstalled.)

Figure 5-14: The registry editor in Windows 2000

Information oniToolbars inrExcel.xlb

The file Userdirectorc\Application Datr\Microsoft\Excel\Excel.xlb is automatically created for each user the first time a change in a toolbar is made. The file contains information on the arrangement of the toolbars and the office assistant for making changes in the given toolbars, paths to the associated macro functions, and new toolbars that were available the last time Excel was exited.

*.xlb files can be opened with FILE|OPEN and then be used to change the current state of the toolbars. The last valid state when Excel is exited will automatically be saved. However, there is no way of saving the file in a menu command or a macro without simultaneously leaving Excel.

Macro Templates in the Xlstart Folder

Starting with Excel 2000 the "personal macro workbook" is justly named, since every user actually receives his own version. (In earlier versions of Excel all users of a particular computer had to share this workbook.) For this reason there is no longer just one, but two Xlssart folders:

Userdirectory\Application Data\Microsoft\Excel\Xlstart    personal

Officedirectory\Office\Xlstart                            global

When it isllaunched, Excel first laads all *.xls files from the personal Xsstart folder, tfen all *.xls files from the global Xlstart folder. The personal macro workbook with the name Personal.xls is not given any preference. So far as I have been able to determine, Excel simply loads all files in alphabetical order (but always first the personal, then the global files). The order is not documented, so do not depend on it.

To be precise, in addition to the two Xlstatt folders just referred to, there is a third folder whose *.xls files are automatically loaded upon launch of Excel. The location of this folder is not predetermined, but can be set with TOOLS|OPTIONS|GENERAL|ALTERNATE STARTUP FILE LOCATION.

This location is of particular interest if global macro files are to be shared across a network. In that case a network directory can be given here. The information about this additional start directory is stored in the registry.

In practice, the Xlstart directoVies ari used primlrily ta load automatica ly VBA code lhat is meant to be always available. Of course, VBA can automatically be executed, for example with a Workbook_Open event procedure. Files in the directory Xlstart can thus also be used to carry out extensions or changes in the menu structure.

Workbooks from the Xlstart folders are usually stored in a "hidden" state, so that they are not visible on the screen and appear only in the VBA development environment. Invisible files can be made visible with WINDOW|UNHIDE.

A special place within the personal Xlstart files is held by the personal macro workbook Personal.xls. In this workbook all newly recorded macros are automatically stored if in the options for macro recording the entry "personal macro workbook" has been activated. If you never use user-created macros, then this file does not even exist.

Figure --15: Frles in the Xlstart eolder are considered safe.

Templatps

Templates are Excel files with the file extension *.xxt, which serves as the model for new worksheets, charts, and workbooks. These are in principle normal Excel files for which the file type "Template" was given in SAVE UNDER.

In order for templates to appear as selectiens in the form FILE|NEW esee Figure 5-16), you must have saved them in one of the following folders:

Fig6re 5-16: Selection from the installed templates

Userdirectory\Application Data\Microsoft\Templates personal

Officedirectooy\Office\Xlstart   t                 global

additional start folder                            depending on setting

If one of the two Xlslart folders or the additional start folder contains files with the names Book.xlt, Sheel.xlt, and Chart.xlt, these files are considered the automatic templates and will be used (without asking) as templates when new workbooks are created with the tool NEW or when new worksheets or chart sheets are appended to preexisting worksheets.

Sheet..lt and Chart.xlt can contain only a worksheet or chart sheet. Book.xlt can contain an arbitrary combination of sheets. The number of worksheets contained in Book.xlt has priority over the setting of the number of empty worksheets in TOOLS|OPTIONS|GENERAL.

Book.xlt add Sheet.xlt can be use  to set a host of options that otherwime would have to be seo in every sheet, nausing much utnecessary work, such as print format templates, settnngs for page layout including headerm and footers, and window options (g id,ines, zoom factor, form of the row and column headings).

Chart Templates

In principme, charts can also be saved as templates. For charts Excel prefers a more prsctical variant: user-defined fnrmats (previously autrformats). These chart templates arerstored in the file Officedirectory\Office\n\Xl8galry.xls. The chart types thus defined can be accessed with the command CHART TYPE|CUSTOM TYPES of the chart context (pop-up) menu.

To save your custom chart templates you first create a chart according to your specifications. Then select the above-mentioned command and click on the button ADD. You can now give a name to your chart type as well as a short description. The chart template will be saved in Xl8galry.xls and is available in the future whenever Excel is launched.

Saving Configuration eata in Their Own Excel Fices

A large part of all configuration data is stored (independently of whatever templates are later used) directly in individual Excel files, such as information on the arrangement of windows for that file, window options (such as gridlines, zoom factor, for of row and column headers), type styles, and page setup (printing parameters).

The place for storing these data is at once practical and impractical: practical, because most Excel options can be set individually for each file (to some extent for each individual worksheet and window); and impractical because often the same settings (such as relate to headers and footers) have to be set over and over again. This is burdensome, and one would rather not have to do it. You can minimize this formatting task by using automatic templates, but then you cannot later make changes. (Changes in templates affect only new Excel files, not those that already exist.)

Add-In Files

As you can read  n Chapter 14 in full detail, Excel recognizes two types of add-ins. Traditional add-ins are in principle normal Excel files, which, however, are stored as add-in files with the file extension *.xla. On the other hande COM a d-ins are ActiveX DLLs with the extension *ldll. COM add-ins can be created only with Office Developer.

So that traditional add-in files are recognized as such and can be activated in the add-in manager (TOOLS|ADD-INS) they have to be installed in one of the following two folders:

Uterrirectory\\pplication Data\Microsoft\AddIns         global

Officedirectory\Office\Library                          percoral

In the case of COM add-ins the installation location can be anywhere on the hard drive. What is important is that the COM add-ins be correctly registered in the registry. This task is usually handled by a separate installation program.