Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Packaging

For simple single-workbook applications, we can just e-mail the workbook to our users and ask them to open it. For more complex applications, we may need to install templates, add-ins or other files in specific directories for Excel to pick up and write specific registry entries to ensure our add-ins are listed and/or installed correctly.

Installation Location

When thinking about how to install our application, we need to consider how the user will start it, create new files and so on. Dictator applications are almost always started by opening one of the workbooks, or starting a VB6 EXE front loader. As such, dictator applications should be installed to a single folder and run from there.

Add-ins can be installed either by copying them to one of two specific locations, or by writing registry entries to include them in the Tools > Add-ins list (see later for details). Copying the xla to the user's AddIns folder is the easiest way to manually install add-ins, but is only appropriate for single-file add-ins. If our add-ins use other supporting files (such as templates, databases and so forth), all the files should be installed to their own folder, with the registry entries written for the Tools > Add-ins list.Chapter 7 Using Class Modules to Create Objects). The main problem with this approach is that the only way to ensure the corresponding add-in is installed is to include code in the template file to check that, which breaks the principle of not including code in our templates. Instead, we can use the AddIn_Install event to copy the template file to the user's templates folder and the AddIn_Uninstall event to remove it.

Many application-specific add-ins (including our PETRAS example) ignore the File > New menu and provide their own menu or toolbar button for creating new instances of their data-entry workbooks. By doing this, we don't need to copy files to different locations for Excel to use them. We can simply install all our files to a single folder and write registry entries to install our add-ins.

Installation Requirements

Templates

Templates that are intended to appear in the File > New dialog are installed on a per-user basis by copying them to the following folder:

C:\Documents and Settings\<UserName>\Application Data\Microsoft\Templates

If our templates are opened under program control (such as the New Timesheet button in our PETRAS timesheet example), they should be kept with the add-in in their own folder.

Add-ins

Single-file, general-purpose add-ins can be installed on a per-user basis by copying them to the following folder:

C:\Documents and Settings\<UserName>\ Application Data\Microsoft\AddIns

or for all users by copying them to the following folder:

C:\Program Files\Microsoft Office\Office\Library

where Microsoft Office is the folder in which Office has been installed.

When add-ins are copied to either of these folders, they automatically appear in the Tools > Add-ins list, but are not installed by default. To have them installed as well as listed requires us to write some registry entries.

For more complex add-ins that may have other support files, it is better to install all the files to their own directory and write registry entries to add them to the Tools > Add-ins list. To do that, we add a string value in the registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager

where the name of the value is the full path and name of the add-in. The 10.0 in the registry key refers to the version of Excel:

9.0 = Excel 2000

10.0 = Excel 2002

11.0 = Excel 2003

When we add entries to the Add-in Manager key, the add-in is listed in the Tools > Add-ins dialog, but is not installed (that is, active). To have the add-in automatically installed, we have to write an entry to the registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options

instead of the Add-in Manager key. The entry must be a string value where the name is the next available item in the sequence OPEN, OPEN1, OPEN2, OPEN3 and so on, and the value is the full path and name of the add-in, surrounded by quotation marks. This means that when writing registry entries to have our add-ins automatically installed, we have to first check whether there is a value called OPEN, then check whether there is a value called OPEN1, then check for OPEN2 and so forth until we find one that isn't used. If we precede the add-in name with a /R switch, Excel will open the add-in read-only. Figure 24-5 shows two OPEN registry entries, for the analysis toolpack and the IfError automation add-in.

Figure 24-5. The OPEN Registry Entries for an Excel and an Automation Add-in

[View full size image]

COM Add-ins

The registry entries required to install COM Add-ins are covered in detail in Chapter 21 Writing Add-ins with Visual Basic 6. The registry entries can be written by the Add-in Designer object, under either:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins

for per-user installation, or:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins

to install the COM add-in for all users. The Designer will write the registry entries when the COM Add-in DLL is registered on the user's machine, which is done using the regsvr32.exe program:

regsvr32 c:\mypath\myCOMAddin.dll

Automation Add-ins

Automation add-ins must also be registered on the user's machine using regsvr32, but they do not write their own registry entries. Instead, we have to write the same entries that we would for normal add-ins, but using the ProgID (that is, ProjectName.ClassName) instead of the file path and name. For example, to install the IfError function from Chapter 21 Writing Add-ins with Visual Basic 6 so it is listed in the Tools > Add-ins list but not installed, we would add a new string value with the name ProExcel.Functions within the registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager

To have the same add-in automatically installed, we would instead write a new value in:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options

where the name is OPEN, OPEN1 or OPEN2 and so forth, and the value is:

/A "ProExcel.Functions"

The /A identifies it as an automation add-in, and the "ProExcel. Functions" is the ProgID of the class containing the IfError function, as shown in Figure 24-5.

Installation Mechanisms

Manual

If our application consists of just a template and associated add-in, we could provide instructions telling the users where to copy the files to, and hope they do it correctly. As the number of files increases, so does the likelihood of failure.

An Installation Workbook

A very common method of installing Excel-based applications is to zip all our files together and tell our users to unzip the file to a new directory and open the Setup.xls workbook. The Setup.xls workbook performs the following tasks, then closes itself. These tasks could also be performed by a VB6 front loader, as described in Chapter 20 Combining Excel and Visual Basic 6:

Registers any DLLs, using:

Shell "regsvr32 /s "" & ThisWorkbook.Path &"\MyDLL.DLL""

Moves add-ins and/or templates to the correct directories using Application.LibraryPath and/or Application.TemplatesPath to identify where to move them to.

Uses the Excel object model to install add-ins (instead of writing registry entries), as shown in Listing 24-9.

Listing 24-9. Installing Add-ins Using the Object Model

Sub InstallAddins()
'Install an Excel Addin
Application.AddIns.Add(ThisWorkbook.Path & _
"\MyAddin.xla").Installed = True
'Silently register an Automation Addin
Shell "regsvr32 /s "" & ThisWorkbook.Path & _
"\ProExcel.dll""
'Install an Automation Addin
Application.AddIns.Add("ProExcel.Functions") _
.Installed = True
End Sub

Windows Installer

The biggest problems with the Manual or Workbook installs is that they do not provide an easy uninstall mechanism and they aren't really "professional." Doing it properly requires us to write an installation routine using InstallShield, WISE or similar application. Unfortunately, they are both expensive purchases and it is beyond the scope of this book to describe how to create installation routines using them. If you intend to use one of these packages, we suggest you refer to the product documentation, specifically to write the registry entries we detailed above.