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 toolbars ,

  • changing and extending the menu system,

  • changing general 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 ToolsOptions, 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 ToolsOptions, 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 TOOLSOPTIONS 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 Settings

The form for setting the printing options is hidden within the menu command FILEPAGE SETUP. This setting normally holds only for the active worksheet. (However, several worksheets can be selected and then set simultaneously .)

With FILESAVE ASTOOLSGENERAL OPTIONS you can set various save options (password, write protection, backup file). The command FILESAVE ASTOOLSWEB OPTIONS opens up a further form with a host of options that govern the conversion of a document to HTML format.

Note  

The file and print options hold only for the active file. Unlike Word, Excel cannot easily be given such default settings as always to make a backup copy when a file is saved or to maintain a particular page format. One possible solution to this problem is the use of templates (see Chapter 9).

Window options are to be found, with one exception, in TOOLSOPTIONSVIEW. The exception is the zoom factor, which is found in VIEWZOOM. 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 VIEWTOOLBARSCUSTOMIZE. (For details see Chapter 8.)

Tip  

Office 2000 has the peculiarity of at first displaying incomplete menus . The missing entries appear only after a while. It seems that in its benevolence Microsoft believes that this arrangement simplifies the life of the user. However, you can undo this nonsense by deactivating the option MENUS SHOW RECENTLY USED COMMANDS FIRST in the form VIEWTOOLBARSCUSTOMIZEOPTIONS .

Some virus protection options are hidden in the form TOOLSMACROSECURITY. 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.)

Setting Options with Program Code

Most Excel options are set with numerous properties of the Application object. Options that do not affect Excel as a whole, but only a file, a window, or a chart, for example, can be changed via the properties of the corresponding object ( Worksheet, Window, etc.), where the association 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 set for every sheet object ( 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.

The following tables give an overview of the most important properties and methods .

APPLICATION OBJECT (GENERAL OPTIONS)

ActivePrinter

set the currently active printer

AddIns( )

access to add-ins

AutoRecover.Enabled

determines whether Excel 2002 should automatically make a backup copy

AutoRecover. Path

specifies the directory for backup copies

Calculation

recalculation of worksheets automatic/manual

CommandBars( )

access to menu bars and toolbars (see Chapter 8)

DisplayAlerts

display alerts

DisplayFormulaBar

formula bar on/off (True/False)

DisplayFullScreen

full screen mode on/off

DisplayNoteIndicators

red markings in cells to indicate notes

DisplayStatusBar

status bar

ErrorCheckingOptions

options for error checking

MoveAfterReturn

cursor moves on Return into the next cell of a table

MoveAfterReturnDirection

direction of cursor movement on Return

OnEvent

various event procedures (see Chapter 4)

PromptForSummaryInformation

form for input of information on saving

ScreenUpdating

update screen during macro execution

SheetsInNewWorkbook

number of empty worksheets in a new file

SmartTagRecognizers.Recognize

activation of the smart tag function (Excel 2002)

SmartTagRecognizers(n).Enabled

activation of individual smart tag modules (Excel 2002) activation of the smart tag module (Excel 2002)

Speech.SpeakCellOnEnter

automatic speech output of the cell's contents

StandardFont

name of the default font in worksheets

StandardFontsize

size of the default font in worksheets

WORKBOOK OBJECT (FILE-SPECIFIC OPTIONS)

ChangeFileAccess

change access privileges

Colors

access file's color palette (56 colors)

CreateBackup

create backup file on saving

DisplayDrawingObjects

display drawing objects

EnableAutoRecover

turn on/off automatic backup copy for the file (Excel 2002)

Protect

turn write protection on and off

SmartTagOptions.DisplaySmartTags

displays smart tags (Excel 2002)

SmartTagOptions.EnableSmartTags

save smart tags with file (Excel 2002)

Styles( )

access to templates

Visible

file visible/invisible (hidden)

WORKSHEET OBJECT (WORKSHEET-SPECIFIC OPTIONS)

DisplayAutomaticPageBreaks

display page breaks in worksheets

EnableAutoFilter

enables display of autofilters

EnableOutlining

enables display of grouping

EnablePivotTable

enables the creation of pivot tables

FilterMode

autofilter on/off

PageSetup

access to page and printer settings

Protection

sheet protection options

SetBackgroundPicture

set background picture

Visible

worksheet is visible/invisible

WINDOW OBJECT (WINDOW-SPECIFIC OPTIONS)

DisplayFormulas

display formulas instead of results

DisplayGridlines

display gridlines

DisplayHeadings

display row and column headings

DisplayHorizontalScrollbar

display horizontal scroll bar

DisplayOutline

display grouping

DisplayZeros

display 0 values (or display empty cell)

DisplayVerticalScrollbar

display vertical scroll bar

DisplayWorkbookTabs

display workbook tabs

FreezePanes

split window frozen/unfrozen

GridLineColor

set color (RGB value) of gridlines

GridLineColorIndex

color of gridlines from the color palette (0 to 55)

PageSetup

access to page and printer settings

Split

window split/not split

SplitColumn

column in which the window is split

SplitRow

row in which the window is split

TabRatio

ratio of tab area to horizontal scroll bar

Zoom

zoom factor

PAGESETUP OBJECT (PAGE LAYOUT, SET SEPARATELY FOR EACH SHEET)

BlackAndWhite

print in black and white

BottomMargin

bottom margin, in points (1/72 inch = 0.35 mm)

CenterFooter

footer, central part

CenterHeader

header, central part

CenterHorizontal

print horizontally centered

CenterVertical

print vertically centered

FirstPageNumber

start value for pagination

FooterMargin

size of footer

HeaderMargin

size of header

LeftFooter

footer, left part

LeftHeader

header, left part

LeftMargin

left margin in points (= 1/72 inch = 0.35 mm)

Orientation

print in vertical or horizontal format

PaperSize

paper size

PrintArea

area of page to be printed

PrintTitleColumns

column title (printed on each page)

PrintTitleRows

row title (printed on each page)

RightFooter

footer, right side

RightHeader

header, right side

RightMargin

right margin in points (= 1/72 inch = 0.35 mm)

TopMargin

top margin in points (= 1/72 inch = 0.35 mm)

DEFAULTWEBOPTIONS (EXCEL GLOBAL) / WEBOPTIONS (FILE-SPECIFIC)

AllowPNG

encode pictures in PNG format

DownloadComponents

download missing web components .

Encoding

desired character set for web browser

LocationOfComponents

location to which web components are to be saved

OrganizeInFolder

save pictures, etc., in their own folder

RelyOnCSS

use Cascading Style Sheets

RelyOnVML

use Vector Markup Language

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 the location to save your personal files.

Of course, there is a reason for describing the fundamentals of Windows in such detail: The user-specific configuration files of Excel are stored in subdirectories of the personal folder. For the remainder of this section this folder will be abbreviated as Userdirectory .

Some additional configuration files are stored relative to the installation folder of Office 2000. This folder will hereinafter be abbreviated as Officedirectory .

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 Userdirectory\Application Data\Microsoft\Excel\Xlstart\Personal.xls .

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

  • Personal templates are stored in Userdirectory\Application Data\Microsoft\Templates .

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

  • Global add-in files are stored in Officedirectory\Office\Library .

  • Personal add-in files, however, are located in Userdirectory\Application Data\Microsoft\AddIns .

  • Predefined (that is, included with Excel) chart templates are stored in Officedirectory\Office\ n \Xl8galry.xls . Here n is the language code.

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

  • Backup copies of all opened files are stored by default in Excel 2002 in the directory Userdirectory\Application Data\Microsoft\Excel\ . This path can be changed (property Application.AutoRecover.Path ).

  • All remaining settings are file-specific and are stored in their own Excel files.

Tip  

File names and paths of configuration files change with every version, not least to avoid conflicts with the simultaneous use of several versions of Office. If you wish to create portable Excel applications, you should not rely on the configuration files being located in any particular place.

Tip  

You can find an overview of Excel properties for accessing most of the above-mentioned folders, in the section of Excel-specific methods and properties.

Settings in the Office 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.0\Excel\InstallRoot HKeyLocalMachine\Software\Microsoft\Office.0\Excel\InstallRoot 

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

 HKey_Current_User\Software\Microsoft\Office.0\Excel\Options HKey_Current_User\Software\Microsoft\Office.0\Excel\Options 

These settings can be edited with the programs RegEdit.exe and RegEdt32.exe (see Figure 5-14). (However, it is not recommended to do so unless you know what you are doing. By changing the registry you can screw up Office and even Windows to such an extent that they will have to be reinstalled.)

click to expand
Figure 5-14: The registry editor in Windows 2000

Information on Toolbars in Excel.xlb

The file Userdirectory\Application Data\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 FILEOPEN 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.

Tip  

It is possible to store user-defined toolbars directly in an Excel file with VIEWTOOLBARSCUSTOMIZETOOLBARSATTACH . This makes sense when the toolbar is to made available to other users (possibly on another computer).

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 Xlstart folders:

 Userdirectory\Application Data\Microsoft\Excel\Xlstart    personal Officedirectory\Office\Xlstart                            global 

When it is launched, Excel first loads all *.xls files from the personal Xlstart folder, then 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 Xlstart 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 TOOLSOPTIONSGENERALALTERNATE 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 directories are used primarily to load automatically VBA code that 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 WINDOWUNHIDE.

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.

Caution  

Excel normally displays a virus warning before files with VBA code are loaded. However, this does not automatically hold for files that come from the Xlstart folder. The reason for this is the option TRUST ALL INSTALLED ADD-INS AND TEMPLATES , which is the default (see Figure 5-15).You can change this in TOOLSMACROSECURITYTRUSTED SOURCES .

This default setting makes sense, of course. It would be a pain in any one of a number of places if every time Excel was launched it asked permission to run the macros that you personally had stored in Personal.xls and other Xlstart folders. On the other hand, one could imagine that a virus programmer would take delight in this breach of security in Excel.

click to expand
Figure 5-15: Files in the Xlstart folder are considered safe.
Tip  

If you wish to launch Excel without having certain files open automatically, you may use the command line option /s . Thus execute STARTRUN and input excel /s .

Templates

Templates are Excel files with the file extension *.xlt , 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 selections in the form FILENEW (see Figure 5-16), you must have saved them in one of the following folders:

click to expand
Figure 5-16: Selection from the installed templates
 Userdirectory\Application Data\Microsoft\Templates personal Officedirectory\Office\Xlstart                     global additional start folder                            depending on setting 

If one of the two Xlstart folders or the additional start folder contains files with the names Book.xlt , Sheet.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.xlt 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 TOOLSOPTIONSGENERAL.

Book.xlt and Sheet.xlt can be used to set a host of options that otherwise would have to be set in every sheet, causing much unnecessary work, such as print format templates, settings for page layout including headers and footers, and window options (gridlines, zoom factor, form of the row and column headings).

Tip  

Templates and their extensive possibilities are described more fully in Chapter 9. There the support of input of formulas by VBA code ( intelligent forms) will be explained.

Chart Templates

In principle, charts can also be saved as templates. For charts Excel prefers a more practical variant: user-defined formats (previously autoformats ). These chart templates are stored in the file Officedirectory\Office\n\Xl8galry.xls . The chart types thus defined can be accessed with the command CHART TYPECUSTOM 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 Data in Their Own Excel Files

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 in 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 hand, COM add-ins are ActiveX DLLs with the extension *.dll . 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 (TOOLSADD-INS) they have to be installed in one of the following two folders:

 Userdirectory\Application Data\Microsoft\AddIns         global Officedirectory\Office\Library                          personal 

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.




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

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