Dutch Gemini's Weblog Pages

January 4, 2012

Install (and Uninstall) an Excel Add-In with InnoSetup

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 9:00 am
Tags: , , , ,

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.

Advertisements

13 Comments »

  1. Can you make it available as a text file?

    Comment by JP — May 5, 2010 @ 4:37 pm | Reply

  2. 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 | Reply

    • 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 | Reply

      • 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

  3. 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 | Reply

  4. 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 | Reply

    • 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 | Reply

  5. 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 | Reply

  6. 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 | Reply

  7. […] 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 | Reply

  8. Hi, I’m highly in need of a 64bit installer for an Excel Add-in – any chance you have a date for your update to this post?

    Comment by reflectiveexpressioncott — November 25, 2015 @ 7:31 am | Reply

    • I promise myself every time that I should update with the script I now use that takes care of the installation on 64-bit systems, though at the moment with 32-bit Excel only due to the target application my add-in connects to (which is 32-bit) but for various reasons I never finalise. I will try to find some time over the next couple of weeks to do.

      Comment by dutchgemini — November 25, 2015 @ 9:06 am | Reply

  9. View 3 Fiber Splicer

    Install (and Uninstall) an Excel Add-In with InnoSetup | Dutch Gemini's Weblog Pages

    Trackback by View 3 Fiber Splicer — August 9, 2016 @ 7:07 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: