Setup program for Excel add-ins

During the development stage of the first version of Connexa XS the need arose for a suitable installation tool for our addin.
I came across this interesting article of Jan Karel Pieterse, a fellow professional Office developer.

The article outlined how to use the product ‘Setup Factory‘ to easily realize a setup file for an Excel add-in. Based on the information in the article and its comments I was able to create a user-friendly and robust installation file for the Connexa product family. I did notice some areas for improvement though, which is incorporated in the information which follows (and by now in the initial article as well).

First some background how Excel handles add-in files like .xlam or .xla:

How Add-ins are Managed in Excel

In Excel, all the user sees is this list of available Add-ins:

You can navigate to this dialog as follows:

  • Excel 2003: Menubar, Tools, Add-ins
  • Excel 2007: Start-button , Excel Options, Add-Ins, drop down: Excel add-ins, Go
  • Excel 2010: File tab, Options, Add-Ins, drop down: Excel add-ins, Go

Which Add-ins are available

Under the hood, Excel keeps score in the registry which Add-ins there are and which are installed.
To build the list in the above dialog, Excel looks in a couple of places:

1. The Add-ins folder:

There are a couple of locations Excel looks for add-ins:

C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns
or altenatively:
C:\Program Files\Microsoft Office\OFFICE11\Library (Excel 2003) ,
C:\Program Files\Microsoft Office\OFFICE12\Library (Excel 2007) etcetera

Any Add-ins in this folder are automatically included in the Add-ins dialog.

2. The registry:

For Add-ins in a different location from the ones shown above, Excel will look in the registry. Keys are added there when you click the browse button to locate an Add-in.

Which Add-ins are Selected

Excel notes what Add-ins are selected. It does so using a number of values in this part of the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

For each selected Add-in,  Excel adds an entry in this location, successively called “OPEN”, “OPEN1”, “OPEN2”,…

These keys each contain the name of the add-in to be opened (and sometimes some command-line parameters). If an add-in is not in the add-ins folder the full path is included, e.g.:
“C:\Program Files\Connexa XS\Connexa v12.48.xlam”

Be advised that these registry entries will only be added / deleted AFTER Excel is closed.
To be complete: an alternative location in the registry exists, containing values for each Add-in to be shown in the Add-ins dialog:

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

The value needed is simply the path to the Add-in, f.i.:
C:\Program Files\Autosafe\autosafe.xla
Since this location plays no role regarding installed add-ins it will not be discussed further.

How To Install An Excel Add-in using Setup Factory

Setup factory does not come with an automatic/wizard driven way to handle the registry entries that need to be produced for Excel, so an Add-in is properly added to the Add-ins dialog and is installed. But it does come with a versatile scripting language that enables you to program these entries yourself quite easily.

Note that only the registry part needs scripting, the remaining stuff (creating the setup screens, configuring how the setup works, where to install your files, what files to install…) is done through easy to use wizards and configuration screens.

Script for registry modifications during Install of an Add-in

The action editor enables you to add actions or scripts to events in Setup Factory. To install an Excel Add-in successfully you need to implement the following actions:

On Startup

The following script is optional but may come in handy if you want the user to close Excel before installing your add-in:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

for iCount, sValue in sValues do
    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then 

        Dialog.Message("Error", "First close Microsoft Excel."..
        String.Char(10)..String.Char(10)..
        "Installer will now abort.",
        MB_OK, MB_ICONINFORMATION);
        Application.Exit(0);
    end;
end;

On Post Install

To make sure no registry entries are changed until all files have been installed successfully, make sure you add this script to the “On Post Install” action in Setup factory.

The script shown below does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in the next registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office

    This information is subsequently stored in the array variable sVersions (each version has a numeric entry here: 11.0 for Excel 2003, 12.0 for Excel 2007 and so on).

  • For each of these versions the script then looks in the Excel/Options part of the registry to determine the maximum index number regarding the OPEN registry entries (if any).
  • After that, the new OPEN registry entry containing the first available index number is added to the registry.

The script assumes you have created a -what Setup Factory calls- “Custom Session variable”, named “AddinFileName”, which contains the filename of your add-in

The script:

-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version
for iCount1, sVersion in sVersions do    
    -- Try opening the registry key
    sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
              "\\Excel\\Options\\"
    sValues = Registry.GetValueNames(2, sSubKey);

    --initialize index counter
    iIndex = -2
    if sValues then

        --Determine the index of the maximimum OPEN registry entry
        for iCount2, sValue in sValues do

            if (String.Left(sValue, 4) == "OPEN") then            
                --Check whether the user did not already install
                --the same add-in to prevent errors when opening Excel
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true)  
                if String.Find(sKeysValue, SessionVar.Expand(
                              "%AddinFileName%"), 1, false) > 0 then
                    iIndex = -1
                    -- leave loop
                    break;
                else
                    if (sValue == "OPEN") then
                        iIndex = 0
                    else
                        iIndex = String.ToNumber(String.Mid(
                                 sValue, 5, String.Length(sValue)-4))
                    end;
                end;
            end;
        end;

        -- -1 means: This add-in is already installed; we're done
        if iIndex ~= -1 then        
            --Determine path based on variable "%AddinFileName%
            sAppPath = String.Char(34)..
                       SessionVar.Expand("%AppFolder%")..
                       "\\"..
                       SessionVar.Expand("%AddinFileName%")..
                       String.Char(34)

            -- -2 is the initialized value of the index counter
            if (iIndex == -2) then
                -- OPEN-key does not exist
                Registry.SetValue(2, sSubKey, "OPEN",
                                  sAppPath, REG_SZ)
            else
                Registry.SetValue(2, sSubKey, "OPEN"..(iIndex + 1),
                                  sAppPath, REG_SZ)
            end;
        end;
    end;
end;

How to uninstall an add-in

Of course Setup Factory also generates an uninstall method, both in the start menu and in the Add/remove programs applet of Windows Control Panel.

A registry change is necessary because the proper Options/OPEN entry has to be removed from the registry in order to prevent startup errors during load of Excel. Again the scripts are shown per Setup Factory action.

On Startup

Again, the following script is optional if you want the user to close Excel before uninstalling your add-in:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

for iCount, sValue in sValues do

    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then                   
        Dialog.Message("Error", "First close Microsoft Excel."..
                        String.Char(10)..String.Char(10)..
                        "Uninstaller will now abort.",
                        MB_OK, MB_ICONINFORMATION);
        -- Make sure the process ends with a value other than 0
        -- so the uninstall can be performed again.
        Application.Exit(EXIT_REASON_USER_ABORTED);
    end;    
end;

On Post Install

The following script does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in this registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office

    (Each version has its own entry here: 11.0 for Excel 2003, 12.0 for Excel 2007 etc.)

  • Delete the registry key regarding our add-in.

The script:

-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version
for iCount1, sVersion in sVersions do

    -- Try opening the registry key
    sValues = Registry.GetValueNames(2,
    "Software\\Microsoft\\Office\\"..sVersion.."\\Excel\\Options");

    if sValues then

        for iCount2, sValue in sValues do

            -- Any installed add-ins present in this Office version?
            if (String.Left(sValue, 4) == "OPEN") then

                sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
                          "\\Excel\\Options\\"
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true)

                -- Delete the registry key if we encounter our add-in
                if String.Find(sKeysValue, SessionVar.Expand(
                               "%AddinFileName%"), 1, false)>0 then
                    --Dialog.Message(sSubKey, sValue) –-*for debugging*
                    Registry.DeleteValue(2, sSubKey, sValue)
                end;
            end;
        end;  
    end;
end;