I have written a script for InnoSetup 5.3, now updated to 5.4.2 (and soon to 5.4.3 made available during the 2011 Xmas holidays), that allows me to install and uninstall an Excel Add-In (XLA or XLAM). Before InnoSetup I was using a VBS script to perform this same task.
This InnoSetup script removes old copies, updates Excel, activates or disables the Add-In, clears the Registry, and more. It also checks and optionally allows setting the “Trust Access To Visual Basic Project” flag under the Macro Security settings (in the Trust Center if on Excel 2007 and 2010).
The nice thing is that now I also have an entry in the Control Panel with which I can uninstall the Add-In (gives a much more professional look).
I have dissected the script I use and have commented the various parts. On the last page I have put the biggest part. If you assemble them you end up with quite a big script , somewhere around 1700 lines of Pascal Script (roughly 1500 lines only for the Code section), considering that only a few files are installed but the script takes care of a large number of issues. In addition, over the years I developed a tendency to comment each instruction carefully, helps when you need to review the code after long periods of times doing something really else.
Here’s the full story. I’ve divided it in pages for easier reading (all links should work now):
Page 1 | Page 2 | Page 3 | Page 4 | Page 5
Small update pending: I have made some changes to the script parts for dealing better with 64-bit versions of Windows and Excel. Expect these changes to be posted within a few days. Thanks for waiting.
Dutch.
Can you make it available as a text file?
Comment by JP — May 5, 2010 @ 4:37 pm |
Hi I have found a shorter 60 ish line script for this, works for office 2007 in winxp and windows7, however I’ve not had any luck getting it to work with excel 2010…. (which would be useful!)
Comment by dan — January 22, 2011 @ 7:43 pm |
Daniel,
When I wrote my script I ended up that long because it contains a lot of checks and comments, makes use of many routines and I had to deal with issues like compatibility with a custom COM server, with additional files in support to the XLA going to the same folder (such as a .CHM and a custom code module engine with related files) and it has the necessary install/uninstall instructions for the deactivation/activation of the Add-In using Excel in automation server (boot Excel, deactivate the add-in, copy the new add-in, reactivate the add-in, etc.). It also contains the necessary instruction and wrapper functions to deal with InnoSetup’s routines that unfortunately do not support all of the exposed functions in installer and uninstaller mode.
If you can provide a link to the 60-liner I could take a look at it and see where it may fail, since I will be going 2010 soon.
Dutch
Comment by dutchgemini — January 23, 2011 @ 6:29 pm |
Hi Dutch,
Arrh i see, I was just dealing with an xlam addin, not a COM, so I just had to add it to excel ‘HKCU’ registry settings.
Appently excel 2010 also should work on the same basis (options key), but I can’t seem to get it to work!
Anyhow it still might be of use to you in some form?
Script below just excluding [Setup];
[Languages]
Name: “english”; MessagesFile: “compiler:Default.isl”
[Files]
Source: MY ADDIN NAME.xlam; DestDir: {app}; AfterInstall: CheckRegistry(true);
[Icons]
Name: “{group}\{cm:UninstallProgram,My Program}”; Filename: “{uninstallexe}”
[Code]
procedure CheckRegistry(Install: Boolean);
var
OfficeVersions: TArrayOfString;
I: Integer;
J: Integer;
Installed: Boolean;
Key: String;
Value: String;
Keys: TArrayOfString;
AlertMessage: String;
CurrentValue: String;
NewKey: String;
KeyNumber: Integer;
NewKeys: TArrayOfString;
begin
// Location of Add-In
Value := ExpandConstant(‘”{app}\MY ADDIN NAME.xlam”‘)
// List office versions
if RegGetSubkeyNames(HKEY_CURRENT_USER, ‘Software\Microsoft\Office’, OfficeVersions) then
begin
// Check all the office version
for I := 0 to GetArrayLength(OfficeVersions)-1 do
begin
// Initialize Installation Info
KeyNumber := 0;
Installed := false;
// Check if Excel is installed and has Options
Key := ‘Software\Microsoft\Office\’ + OfficeVersions[I] + ‘\Excel\Options’;
if RegKeyExists(HKEY_CURRENT_USER, Key) then
begin
// List all the add-ins currently being shown – for this read all value name
if RegGetValueNames(HKEY_CURRENT_USER, Key, Keys) then
begin
// Process each value name
for J := 0 to GetArrayLength(Keys)-1 do
begin
// Check if it is really an ADD-IN
if (Length(Keys[J]) >= 4) AND (Copy(Keys[J], 1, 4) = ‘OPEN’) then
begin
// Read the add-in path
if RegQueryStringValue(HKEY_CURRENT_USER, Key, Keys[J], CurrentValue) then
begin
// Check if it is the add-in we are installing
if CompareText(Value, CurrentValue) = 0 then
Installed := true
else
begin
// Store all other add-ins in another array
// Will be used for uninstall
SetArrayLength(NewKeys, KeyNumber + 1);
NewKeys[KeyNumber] := CurrentValue;
KeyNumber := KeyNumber + 1;
end
end
end
end
end
if Installed then
begin
// Are we trying to uninstall?
if not Install then
begin
// Re-serialize all other add-ins
for J := 0 to KeyNumber-1 do
begin
NewKey := ‘OPEN’;
if J > 0 then
NewKey := NewKey + IntToStr(J);
RegWriteStringValue(HKEY_CURRENT_USER, Key, NewKey, NewKeys[J]);
end
// Delete additional keys
repeat
NewKey := ‘OPEN’;
if J > 0 then
NewKey := NewKey + IntToStr(J);
until (Not RegDeleteValue(HKEY_CURRENT_USER, Key, NewKey))
end
AlertMessage := ‘Installed’
end
else // Not installed
begin
// We are trying to install – add to the last
if Install then
begin
NewKey := ‘OPEN’;
if KeyNumber > 0 then
NewKey := NewKey + IntToStr(KeyNumber);
RegWriteStringValue(HKEY_CURRENT_USER, Key, NewKey, Value);
end
AlertMessage := ‘Not-Installed’;
end
AlertMessage := ‘For office version ‘ + OfficeVersions[I] + ‘, add-in is ‘ + AlertMessage;
//MsgBox(AlertMessage, mbInformation, MB_OK);
end
end
end
end;
procedure CurUninstallStepChanged(CurUninstallStep: TUninstallStep);
begin
case CurUninstallStep of
usPostUninstall:
begin
CheckRegistry(false);
end;
end;
end;
Comment by dan — January 26, 2011 @ 6:24 pm
Hi Dan,
I think I’ve been able to understand the problem.
The “OPEN” key in HKCU is used in case the XLAM is located in the default folders and cannot be used when the file is located in another folder (eg. “C:\MyAddIn”).
When an Add-In is installed -using Excel interactively- from a removable drive, then Excel copies it automatically into the ‘Microsoft\AddIns’ subfolder of the special folder called ‘%AppData%’.
On XP this is usually ‘C:\Documents and Settings\“Login Name”\Application Data\Microsoft\AddIns’, on Windows Vista and 7 this would be ‘C:\Users\“Login Name”\AppData\Roaming\Microsoft\AddIns’.
In this mode and with the “OPEN” key containing only the bare name, Excel knowns enough for being able to find and load the Add-In.
However, if the installation is performed from a local drive, then Excel will link the Add-In reference to the file found in the installation folder. Linked Add-Ins are normally stored in ‘HKCU\Software\Microsoft\Office\“Excel Version”\Excel\Add-in Manager’ by name and with full path.
If that’s your case, then the above installation script will not allow Excel loading the Add-In since it will look for your XLAM somewhere else (i.e. below the %AppData% folder). Excel is discrete and will ignore “OPEN” entries pointing to missing files during boot and delete invalid entries automatically when shutting down.
You can try by yourself: put the XLAM in a custom folder on a local drive and hook it to Excel interactively (Office Button, Excel Options, etc.) and then look at the registry.
In my code I do indeed copy the XLAM to one of Excel’s Add-In folders and use Excel as automation server to load and enable it for me.
Dutch
PS: my Excel Add-Ins are of type Excel Macro Add-In XLA/XLAM
Comment by dutchgemini — January 27, 2011 @ 9:49 am |
Can any one share any documentation and the script on how to use inno setup to install a regular XLA and/or XLAM add in?
Thanks in advance
Comment by Luis Gonzalez — February 19, 2011 @ 12:43 pm |
I will (and promised also) as soon as my work schedule allows me some spare time to adapt and comment the script I’m using for my own xla’s/xlam’s so that it is suited for more general use.
Dutch
Comment by dutchgemini — February 21, 2011 @ 3:19 pm |
Dutch, I would also love to see your script! I have an addin that relies on some other COM components and libraries being installed so I’d like to package the whole lot up into a single installer rather than an installer for the library code and a set of instructions for the XLA.
I could simply add the registry info to a simple script but from your post it seems you have a very cool bit of code that will cope with newer versions of Excel and Trust the VB Project too.
Much appreciated,
Martin.
Comment by Martin — February 21, 2011 @ 4:05 pm |
Hi Dutch, interesting blog. I’ve been playing around with the idea of using Inno Set-up to either install an add-in or turn on Trust VBAProject and export/import updated VBA Modules into an excel file. So, I would really appreciate being able to see your code or at least snippets of it. Thanks!
Comment by Brian — September 3, 2011 @ 7:24 am |
[...] over at Dutch Gemini’s website he has helped with plenty of the code to get there. Very well done I might [...]
Pingback by » Distributing Add-ins via Installer Spreadsheet Budget and Consulting — January 27, 2012 @ 7:35 pm |