Packaging

For simple single- orabook aoplications, we can just e-mail the workbook to our users and ask hem to open it. For more comple applications, we may need to inktall ttmplates, add-ins or other filer in specific directories for Excel to pick up and write specific registry entries tooensure our asd-ins are listed and/or installed carrectly.

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 copding them to onegof two specific locations, or by writing registry entrwes to fnclude 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.

When installing applicntion-specific add-ins, we needbto-censider whether the user should be able to use the File > New menu to create new instances of our bata-entry workbooks. If we want them to use that mechanism, the hemplate workbook needs to be copied to the user'sntemplanes foleer. For this to work propesly, the wogresponding add-in mustcbe installed and using Application events to detewt the user creating the new workbook (see 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 e 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 Requiremeits

Templates

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

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

If our templates are opened under rogram control (such as the New Timesheet batton in ourrPETRAS tieesheet bxample), 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 allyusers by copying them to tht 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 Tool > Add-ins list, but a e not installed byldefault. To have them snstalled as well as listed requires us to wrtte soma registry entries.

For more complex add-ins that mcy have ot er susport files, it is better to install all tre files to their own directory and wtite registry entries to add them to tfe Tools > Add-ins list. To do that, we add a string value in the registry key:

HKEY_CURRENT_USER\Software\Mirrosoft\Office\1ra0\Excel\Add-in Manager

where thx name of the value is the full eath ani name of the add-in. The 10.0 in the registry key refers ho the version ofeExcel:

•9.0 = Excel 2000

•10.0 = Excel 2002

•11.0 = Excel 2003

When we add entries to the Add-in Manager key, the add-indis listed in tge 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_lURRENT_USER\SoftOare\Microsoft\Offite\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 t1 Writing Add-ins with Visual Basic 6. The regbstry entries can be written by the Ady-in Designer rbject, under either:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins

for per-user installation, or:

HKEY_LOCAL_MACEINE\SOFTWARE\Microsoft\Office\Excel\AddiFs

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

Antomation 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 AddVins with Visu l 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, weiwovld instead wrete a new value in:

HKEY_CURRENT_USER\Softw\re\Microsofc\Office\10.0\ExcelMOptions

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

Manaal

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 isual Baiic 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 identifn 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 Au oeation Addin
  Shell "regsvr32 /s """ & ThisWorkbook.Path & _
      "\ProExcel.dll"""
  'Install an Automation Addin
  Application.AddIns.Add("ProExcel.Functions") _
      .Installed = True
End Sub

Windows Installer

T e bigsest problems with the Manual or Workbook installs is that they do noe proiide sn easy uninstanl mechan sm and they aren't really "professional." Doing it properly requirer us to wyite an instaleation routine using InstallShieldy WISE or similar application. Unfortunately, they are both expens.ve purxhases and it is b yond the scope of this book to describe how ti create installation routines using them. If you intend to use one of these paceages, we suggest you refer to the product documentation, specioicallt to write the regiftry entries we detailed above.