Dutch Gemini's Weblog Pages

September 5, 2011

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

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 1:50 pm
Tags: , , , ,

Introduction

Installing an Excel Add-In using Excel interactively is a pretty straight forward action and is completed within a few steps independent of the version of Excel used (2000-2010). It takes care of copying the Add-In to the correct folder on your hard drive (which is not always needed, we will see this later), it allows enabling/disabling the Add-In in a guided manner via a built-in Dialog and makes sure its state is preserved each time you restart Excel.

The problems start when your Add-In relies on additional files, for instance a .INI file or a .CHM help file (mine do). At this point, either your Add-In is self-sufficient (creating the missing files automatically or knowing where to find them) or you have to carefully design the way your Add-In is installed to a target PC and hooked to Excel.

Finally, the other thing not really supported by Excel is the uninstallation of an Add-In. As a matter of fact this is indeed not possible, the only thing you can do is to disable an Add-In but it will continue to appear in Excel as long as the Add-In is physically on your hard drive.

To replicate the behaviour and to overcome the limitations set by Excel, I worked out a script in VBScript —made easy when you know VBA— which did the job in an excellent manner but had two main drawback: a) everyone could read and alter my script, and b) it required being ably to run a scripting engine on the target PC which under restricted security policies was not always possible.

Therefore, I decided to migrate my scripts to a tool that allowed me to create self-contained installation packages. After reviewing a few, I decided to go for InnoSetup as it offers extensive customisation options but above all a scripting engine and compiler for customising the installation process to the finest grain. Exactly what I needed. And I wasn’t afraid of using Pascal again (Inno is “based” on this language) after years.

The Beginning

InnoSetup has a wizard that allows creating a basic installation scripts for applications, but this script is not really useful for installing Excel Add-Ins as these [usually] not involve executable or system binary files. I nevertheless decided to create a basic script and work it out to my needs, taking the logic from my original VBScript and rewriting it in Pascal Script. This has been much more fun and challenging than I’d expected in the first place.

The Logic

Installing an Excel Add-In programmatically —and outside Excel— needs to take care of the following issues (not in chronological order):

  • Add-In Installation folder
  • All Users or Current User installation
  • Localisation
  • Registry settings
  • Installation or uninstallation process
  • Additional files needed by the Add-In (configuration, help, references, samples, …)
  • Compatibility versus installed components (ActiveX, COM, …) or versus custom applications
  • Version of Excel on board (2000, 2002, 2003, 2007, 2010, 32- or 64-bit)
  • Security and Trust settings of Excel (aka Macro Security)
  • Version of Windows (XP, Vista, 7, Server releases, …)
  • Excel running or not
  • Starting and stopping Excel as an automation server
  • Visual feedback
  • Activation success or failure
  • Activation via Excel or via Registry
  • Silent or Verbose execution of the installer
  • First installation or update installation

The above shall all fit nicely in the installation logic and framework of InnoSetup.

Specific Issues

Version of Windows — For providing user feedback, the script makes use of message boxes. Starting Vista, these message boxes have become UI-aware, that is, based on the current DPI setting of the screen. Because of this, messages are automatically reformatted and will wrap in unpredictable positions, while respecting hard carriage-returns embedded in the message’s text, with often horrible results. For that reason, the script can test which version of Windows is available and embed either a space (Vista and above) or a carriage return (for all others) for proper wrapping.

Version of Excel — Starting Excel 2007, a new file format is available for Add-Ins, the .XLAM format. So if you program for different versions of Excel (I do) and you have both formats available, then you can install the appropriate version of the Add-In. Knowing the version of Excel is also important while scanning the system’s Registry for specific keys and settings (e.g. 32- or 64-bit Excel).

Trust Settings — Every release of Excel has particular Trust Settings. These settings affect the way Excel considers your code (whether macros embedded in a workbook or an Add-In) safe and will allow it to run. Trust settings are stored in the Registry and allow you to notify your customer if the actual settings are too restrictive for your Add-In. And if manipulated correctly, also enable them automatically upon need.

Excel running or not — You cannot install an Add-In programmatically while Excel is running because if will void whatever setting you make to the system once Excel is stopped. However, Excel is a formidable partner when it comes to providing information about where to install the Add-In (i.e. the LibraryPath or the UserLibraryPath) which is not available otherwise. So sometimes you need Excel but sometimes you can’t continue if it’s there. For that purpose, you need to be able to detect whether Excel is running or not.

Dependencies — The Add-Ins I develop interface a third-party software by means of specific API. Since this API is version dependent, I need to make sure that I do not install the Add-In on a system that does not have the correct release. While developing, I have also encountered many problems related to standard software components on the target PCs, such as crashes related to outdated VBE6.DLL, missing or unusable ActiveX components (a.o. Common Dialogs). For that reason I check in the script if what I need is available and react upon.

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

Blog at WordPress.com.

%d bloggers like this: