Subscribe in a readerInstalling 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:
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 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.
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 add-in.
The script assumes you have created a -what Setup Factory calls- "Custom Session variable", named "AddinFileName", which contains the filename of your add-in.
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 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.
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.
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 34 in total (Show All Comments):Comment by: Jan Karel Pieterse (12/23/2009 4:15:00 AM)In order to make things work smoothly, I suggest to keep the numbers in the registry ascending: Open, Open1, Open2, ...
Note that these are the registry key names, NOT the names of the addins themselves!
Comment by: Stein Magne Nilsen (1/21/2010 8:32:46 AM)Every time I open my excel worksheets and from different computers I need to update links to the location on my add-in file.
I have stored my add-in on c:\ on both computers.
I thought the add-in setup should handle even different locations on add-ins??
Comment by: Jan Karel Pieterse (1/21/2010 8:50:28 AM)Hi Steine,
Have a look at this page:
http://www.jkp-ads.com/Articles/FixLinks2UDF.asp
Comment by: Stein Magne Nilsen (2/2/2010 10:45:09 PM)I've had problems reading values from formula cells in vba by using references to the sheet like this:
ActiveSheet.Range("A:E").Cells(3,5). Where Column 5 is formula cells. It works when I use direct reference like this: ActiveSheet.Range("E5") but that is more difficult to handle i code.
When I debug and stop the code, I see that all the cells in that column return Empty value. In the Immediate window I can read the value though like this:
? ActiveSheet.Range("A:E").Cells(3,5), but not in the debug window.
I've tried to run Application.CalculateFull before reading the range, but no respons. It actually works when I edit the formula cells by dragging and copy the formula in that column. But if I edit other cells after that, they return Empty again in code. I've tried this on excel 2003 on several computers.
Comment by: Jan Karel Pieterse (2/3/2010 12:12:23 AM)Hi Stein,
Maybe you need to add the Value property:
Also, why add the range, since your range starts in cell
A1, this should be the same:
Comment by: Stein Magne Nilsen (2/3/2010 5:11:08 AM)Thanks Jan Karel.
I've tried to add .value, but no response.
I use Range as parameter in the function. I need the Range reference to make it flexible.
It's strange that I can read the value from the formula cells in the immediate window, and not in the code window while debugging.
Comment by: Jan Karel Pieterse (2/3/2010 6:24:21 AM)Hi Stein,
Is your function by any chance called from a worksheet cell?
Maybe you could email me the file and explain what it does not do that you want done?
Comment by: Stein Magne Nilsen (2/3/2010 9:19:32 AM)It is called from a worksheet cell.
I will email you a part of the code where the problem is.
Have a question, comment or suggestion? Then please use this form.