Installing An Excel Add-in Using Setup Factory

Introduction

Being an MVP has some benefits. One of them is that some software companies offer Not For Resale (NFR) products to them for free. Of course these companies hope the MVP's will advocate their products.

Well, for this one I will do so:

Setup Factory 7.0 allows you to rapidly create rock-solid, reliable software installers using a visual drag and drop environment. This compact, single-file setup.exe runs on any version of Windows from 95 to Vista.

I must say I'm most impressed with it.

Some Excel background first!

How Add-ins are Managed in Excel

In Excel, all the user sees is the list of available Add-ins when he selects Tools, Add-ins. Here is what my Add-ins dialog looks like:

Tools, Add-ins list of Excel

What 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:

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

or altenatively:

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

This is for Excel 2003, for Excel XP this would be:

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

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

2. The registry, in this location:

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.

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

So for Excel XP this is:

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

In this location, there is a value for each Add-in to be shown in the Add-ins dialog. The value needed is simply the path to the Add-in, e.g.:

C:\Program Files\Autosafe\autosafe.xla

Part of the registry that handles Add-ins that are not in the default location

Which Add-ins are Selected

In another location in the registry, 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\10.0\Excel\Options

For each selected Add-in, Excel adds an entry in this location, successively called "OPEN", "OPEN1", "OPEN2",...

Registry showing entries for Add-ins that are selected

These keys each contain the name of the addin to be opened (and sometimes some command-line parameters). If an addin is not in the addins folder the full path is included.

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.

I've spent maybe 4 hours to figure this out and I had never used the scripting bit of Setup Factory before. 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

I've written the script shown below which 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 a numeric entry here: 9.0 for Excel 2000, 10.0 for XP and so on.)
For each of these versions the script then looks in the Excel/Options part of the registry whether the addin-to-be-installed is already listed there. If so, the key is noted and removed, but will be recreated later on in he script.
After that, the same is done for the entries in the Addin-manager location of the registry: look if the addin-to-be-installed is present and if so, remove the Value from the registry.
Now that old versions are removed from the registry, the script can add the values and keys for the newly installed addin.

The script assumes you have created a -what Setup Factory calls- "Custom Session variable", named "AddinFileName", which contains the filename of your addin.

versions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");
openindex={}
sFilename=SessionVar.Expand("%AddinFileName%");
for Count1,Version in versions do
    result = Registry.GetValueNames(2, "Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options");
    openindex[Count1]=-1
    bFound=false
    if result then
        for Count2,Value in result do
            if not bFound then
                if (String.Left(Value,4)=="OPEN") then
                    if String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))>openindex[Count1] then
                        sKey=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)
                        if String.Find(sKey,sFilename,1,false)>0 then
                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                            bFound=true
                        else
                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                        end;
                    end;
                end;
            end;
        end;
        sAppPath=SessionVar.Expand("%AppFolder%").."\\"..sFilename
        sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"
        sAddinmgrKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Add-in Manager\\"

        AddinMgrList=Registry.GetValueNames(2,sAddinmgrKey)
        if AddinMgrList then
            for Count1, AddinmgrValue in AddinMgrList do
                if String.Find(AddinmgrValue,sFilename,1,false) then
                    Registry.DeleteValue(2,sAddinmgrKey,AddinMgrList[Count1])
                end;
            end;
        end;
        Registry.SetValue(2,sAddinmgrKey,sAppPath,"",REG_SZ)
        if bFound then
            if openindex[Count1]==0 then
                Registry.SetValue(2,sKey,"OPEN",String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)
            else
                Registry.SetValue(2,sKey,String.Concat("OPEN",openindex[Count1]),String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)
            end;
        else
            if openindex[Count1]==0 then
                Registry.SetValue(2,sKey,"OPEN",String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)
            else
                Registry.SetValue(2,sKey,String.Concat("OPEN",openindex[Count1]+1),String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)
            end;
        end;
    end;
end;

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.

How to uninstall an addin

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.

Again, some registry changes are necessary because the proper Add-in Manager entry and the Options/OPEN entry have to be removed from the registry in order to prevent startup errors during load of Excel.

For this I wrote the script shown below, to be run at "On Post Uninstall".

It 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 a numeric entry here: 9.0 for Excel 2000, 10.0 for XP and so on.)
For each of these versions the script then looks in the Excel/Options part of the registry whether the addin-to-be-uninstalled is listed. If found, the key is removed.
After that, the same is done for the entries in the Addin-manager location of the registry: look if the addin-to-be-uninstalled is present and if so, remove the Value from the registry.

versions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");
openindex={}
sFilename=SessionVar.Expand("%AddinFileName%");
for Count1,Version in versions do
    result = Registry.GetValueNames(2, "Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options");
    openindex[Count1]=-1
    bFound=false
    if result then
        for Count2,Value in result do
            if not bFound then
                if (String.Left(Value,4)=="OPEN") then
                    if String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))>openindex[Count1] then
                        sKey=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)
                        if String.Find(sKey,sFilename,1,false)>0 then
                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                            bFound=true
                        else
                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                        end;
                    end;
                end;
            end;
        end;
        sAppPath=SessionVar.Expand("%AppFolder%").."\\"..sFilename
        sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"
        sAddinmgrKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Add-in Manager\\"

        AddinMgrList=Registry.GetValueNames(2,sAddinmgrKey)
        if AddinMgrList then
            for Count1, AddinmgrValue in AddinMgrList do
                if String.Find(AddinmgrValue,sFilename,1,false) then
                    Registry.DeleteValue(2,sAddinmgrKey,AddinMgrList[Count1])
                end;
            end;
        end;
        if bFound then
            if openindex[Count1]==0 then
                Registry.DeleteValue(2,sKey,"OPEN")
            else
                Registry.DeleteValue(2,sKey,"OPEN"..openindex[Count1])
            end;
        end;
    end;
end;

Comments

Showing last 8 comments of 23 in total (Show All Comments):

Comment by: JamesCooks (11/24/2007 10:35:44 AM)

Nice.
Keep up the great work.

Comment by: Jeroen Vreman (12/7/2007 4:41:44 AM)

If you want to install an add-in for all users then use the script below.
The script create a shortcut to your add-in file in the XLStart folder.
The add-in will be opened when Excel is starting.

Regards,
Jeroen Vreman

Install script:
-- find XLStart folder in microsoft office version(s)
sAppPath=SessionVar.Expand("%AppFolder%").."\\"..SessionVar.Expand("%AddinFileName%")
sMsOfficePath=SessionVar.Expand("%ProgramFilesFolder%").."\\Microsoft Office"

result = Folder.Find(sMsOfficePath,"XLStart",true,"");

-- create a shortcut of the add-in file in XLStart folder(s)
if result then
for Count1,Value in result do
Shell.CreateShortcut(Value,"Add-In Tools",sAppPath);
end;
end;

UnInstall script:
-- find XLStart folder in microsoft office version(s)
sMsOfficePath=SessionVar.Expand("%ProgramFilesFolder%").."\\Microsoft Office\\"

result = Folder.Find(sMsOfficePath,"XLStart",true,"");

-- delete the shortcut(s) of the add-in file in XLStart folder(s)
if result then
for Count1,Value in result do
Shell.DeleteShortcut(Value,"Add-In Tools")
end;
end;

Delete User Settings in registry key 'VB and VBA Program Settings' for all users:
-- delete user settings if selected
nSelectedControl = String.ToNumber(SessionVar.Expand("%UserSettings%"));
if (nSelectedControl == CTRL_RADIO_BUTTON_01) then

sKey = "SYSTEM\\ControlSet001\\Control\\hivelist\\"
result = Registry.GetValueNames(3,sKey);

for Count1,Value in result do
Value = String.Right(Value,String.Length(Value)-10);

if (String.Left(Value,4) == "USER") then
Value = String.Right(Value,String.Length(Value)-5);

sKey = Value.."\\Software\\VB and VBA Program Settings\\"..SessionVar.Expand("%ProductName%").."\\";
Registry.DeleteKey(4,sKey);
end;
end;
end;

Comment by: Ivan (2/6/2008 4:05:16 PM)

thanks for letting me view your guest book and giving me all the information

Comment by: swapnil (3/26/2008 2:02:25 PM)

Hi Jan.
Thanks for the imformative article. If I want to add an addin from a network drive without actually copying it to the local pc then how would I do that with the script?
Thanks again.

Comment by: Jan karel Pieterse (3/27/2008 8:17:49 AM)

Hi Swapnil,

I assume you're using SUF for this. In the Setup factory session variables you enter the right destination path for the add-in. But I think you'd have to do this differently, since your setup program does not have to copy the addin to the network location each time (just once), it just has to set the proper registry entries.

You need two steps:

1. Copy xla to network folder (set its property to readonly, so you can easily replace it when needed)
2. Create logon script for users that sets the registry entries.

Comment by: ToniKiguige (10/22/2008 12:40:58 AM)

Hello guys, I'm new to this web site. I never realized there are such active communities on the internet. I like what's shared here by looking at the first few pages. I hope to share some of my past experiences.

Thanks for reading.
_______________________________________
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

Comment by: Rubberduckone (11/12/2008 1:05:14 AM)

Hi Jan
Appreciate the informative, short and precise article - excellent!

Looking at my registry, to see what is actually set as 'Selected' (the 'Options' key)

I wonder... - some of the Data have a parameter in front - like /F "C:\Program Files\... or \R "C:\Program Files\...

Do you know what the parameters mean and how many are there??

TIA

Comment by: Jan Karel Pieterse (11/12/2008 1:37:04 AM)

Hi Rubberduckone,

No, not quite. I suspect it may have something to do with whether or not the addin should fully load, or just load it's userinterface and then only fully load when any option of the addin is clicked.