Dutch Gemini's Weblog Pages

September 5, 2011

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

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 2:47 pm
Tags: , , , ,

Checking Trust Settings (or Macro Security Settings)

As most know, Excel will permit running a single line of VBA code only after you have decided that you trust the code and that you trust access to VB Projects contained in your workbooks; an Add-In is just another flavour of a workbook and is subject to the same restictions. Trust to VBA code is set via Trust Settings (Excel 2007 and higher, in earlier releases of Excel they are called Macro Security Settings).

Although there seem to be ways to make Excel run the Add-In without touching the Trust Settings, for instance using certificates, I feel that the best option is to make the system run the way it was designed, with Trust Settings set in the correct manner. Corporate IT administrators appear to like this, as they may control them with Group Policies or other types of access control.

I have decided to check for the Trust Settings during the installation process because often my customers have complained that the Add-In refuses to run, triggering all kind of errors that I have later found out were all caused by incorrect Trust Settings. The script contains the necessary code for manipulating the trust settings programmatically, but I’ve decided not to use this opportunity and leave them untouched when I install or uninstall, in line with most corporate IT policies.

[Not] Copying the Add-In files

I decided not to let InnoSetup copy the Add-In (XLA or XLAM) and its support files (CHM, BIN, DAT, …) immediately to the destination folder on the PC but use the dontcopy flag instead and extract them from the package to a temporary folder and copy them to the final destination programmatically. This gives me more control when dealing with different versions of Excel.

Installation Sequence

Trimming down to the very basic, I have identified these major actions, steps and the chronological order of things:

  1. Pre-Install (before Inno even copies a single file)
    1. Check VBE6.DLL version and break out if needed
  2. Post-Install (after Inno has finished copying the files)
    1. Unhook the Add-In from Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In in the collection of Add-Ins
      4. Store its name and disable Add-In
      5. Close Excel
    2. Remove the old Add-In
      1. Physically delete Add-In from Add-Ins folder
      2. Remove Add-In reference from Registry
    3. Copy the new Add-In
      1. Extract Add-In and support files from package to temporary folder
      2. Force existence of Add-In destination folders (seen missing on French version of Excel)
      3. Copy files from temporary folder to Add-Ins destination folder
    4. Hook the Add-In into Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In in the collection of Add-Ins
      4. Enable the Add-In
      5. Close Excel
  3. Success-Install (when Inno has finished)
    1. Check the Trust settings
    2. Set the Trust settings (optional)

Uninstallation Sequence

Trimming down to the very basic, I have identified these major actions, steps and the chronological order of things:

  1. Pre-UnInstall (before Inno removes the stuff it installed)
    1. <nothing for the moment>
  2. Post-UnInstall (after Inno has finished removing its own stuff)
    1. Unhook the Add-In from Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In
      4. Store its name and disable Add-In
      5. Close Excel
    2. Remove the old Add-In
      1. Physically delete Add-In from folder
      2. Remove Add-In reference from Registry

As you can see, there are parts in common between the installation and uninstallation. This was the main reason for making the script modular.

Let’s focus on the main steps.

Hooking the Add-In into Excel

Hooking the Add-In from Excel consists in locating the Add-In in the collection of Add-Ins managed by Excel and enabling it. The Add-Ins collection is part of the Application object. To locate an Add-In, you scan the collection and identify the individual Add-In either by a Name or by the Add-In’s file name. I usually use the name, which corresponds to the one advertised by Excel in its Add-Ins dialogs.

Once you have located the Add-In, you enable it by setting the .Enabled property to True.

Now, this works only if the Add-In is already visible in Excel, that is, Excel has found the Add-In on your hard drive but did not load it yet. To make Excel see the Add-In, put it in a particular folder. See Copying the Add-In for more details.

Editor’s note: there are ways to load an Add-In on-the-fly and let Excel handle most of the stuff, but this is not what we want to achieve in our installer.

Unhooking the Add-In from Excel

Unhooking the Add-In from Excel consists in locating the Add-In in the collection of Add-Ins managed by Excel and disabling it. The Add-Ins collection is part of the Application object.

Once you have located the Add-In, you disable it by setting the .Enabled property to False.

Excel will unload the Add-In from memory and make sure the next time Excel is booted, it will not turn up again. However, this does not fully remove the Add-In from Excel but keep it dormient. To fully remove it, you must remove the file from the hard drive (see Removing the Add-In below).

Copying the Add-In

To make Excel aware of an Add-In it must be copied to one of the working folders for Add-Ins. Although it is in theory possible to drop an Add-In on whatever local and non-removable drive (Excel makes a copy of the Add-In if you try to use one located on a remote or removable drive), in practice though it is recommended to use one of Excel’s “standard” folders, accessible via 2 properties on the Application object:

  1. UserLibraryPath — this is the path for Add-Ins that are visible to the Current User only
  2. LibraryPath — this is the path for Add-Ins that are visible to ALL users

Removing the Add-In

To permanently remove an Add-In from Excel you need to physically remove the file from Excel’s working folders for Add-Ins (see above).

Previous Page | Next Page

Advertisements

Leave a Comment »

No comments yet.

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

Create a free website or blog at WordPress.com.

%d bloggers like this: