Installing An Excel Add-in Using Setup Factory
Posted by Jan Karel Pieterse on March 27th, 2007
Â
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.
I’ve started using this tool just a week ago and 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
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;
This post originates from and is maintained at: www.jkp-ads.com/articles/AddinsAndSetupFactory.asp
Regards,
Jan Karel Pieterse