Dutch Gemini's Weblog Pages

September 12, 2013

Undocumented QueryClose enum ‘vbQueryClose’ value of ‘5’

Filed under: Excel,Programming,VBA,Windows — dutchgemini @ 2:39 pm

In my Excel 2013 32-bit VBA application, while monitoring the ‘UserForm_QueryClose‘ event, Excel threw up a value of ‘5’.

So far I am acquainted with vbQueryClose enums ‘0’ … ‘4’ which are documented by Microsoft (http://msdn.microsoft.com/en-us/library/ee177104.aspx) but never traced nor saw a ‘5’. Not a single hit on the Internet, this value is a genuine mystery to me.

This value of ‘5’ occurs during closing down an Excel window —either single window workbook or multiple window workbook— to which a modeless window is hooked.

Excel 2013, being now an SDI application, causes all kind of Z-Order issues with legacy application using UserForms which are well documented on various Excel MVP sites. For instance Jan Karel Pieterse has dedicated time and resources to document and provide workarounds and solutions.

During my programming work, I have seen that modeless UserForms are linked to the window active at the time the UserForm was instantiated. Up to Excel 2010, all windows share the same Windows window handle called Application.HWnd. In Excel 2013 each Excel window has its own Windows window handle property called HWnd, which is equivalent to Application.ActiveWindow.HWnd but finally reduces to Application.HWnd. Hence there is no single Application.HWnd any more in Excel 2013, but its value changes when a different Excel window becomes the active one.

In order to show always on top a modeless UserForm created in an XLAM Add-In, I have used a Win32 API called SetWindowLong() which hooks the modeless userform to the active Excel window when I switch between them.

Because the modeless UserForm shall not be killed when an Excel window is closed, as long as the application is living, that is, excel.exe is running, I do not unload the UserForm but keep it alive with all the data inside by hiding it, using the form’s event called UserForm_QueryClose(). In this routine I detect the ‘CloseMode‘ value and set ‘Cancel = True‘ if necessary.

If I close an Excel window on 2013, first of all I receive value ‘5’ which I do not know how to handle. On top of it setting ‘Cancel = True‘ also does not prevent the UserForm from unloading, it is killed without further notice or errors, leaving my application crippled.

I would like to know what this value ‘5’ is about, maybe it’s new to Excel 2013? Are there other undocumented values and what are they designed for? Can someone help?

Dutch

February 26, 2013

Show a specific page of a CHM help file from your Office application with VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 11:20 am

Update for users of Office 2010: starting this version things have changed slightly, and the information provided here does not work anymore.  I have added the necessary modifications at the bottom of this article.

Users of a pre-2010 version of Office may benefit, too.


This thread is not about creating a help file for your VBA application – there are many other pages on the Internet that tell or sell better solutions than you will find here – but about accessing pages of a CHM via VBA code.

As most programmers know, a strong added value of any application developed is on-line help. In the application I develop I had the need to provide some on-line help on the format that one could use for customising the display and printout of dates.

Office (XP/2003/2007) VBA provides a method called ‘Application.Help’. This method has 2 parameters:

  1. (string) The name of the help file, either HLP or CHM
  2. (long) The Help Context ID

Thanks to Ron De Bruin – a dutch MVP making tons of great tips available to the public  – I obtained the list of Help Context ID’s that give access to Excel’s Help pages.

However, I could not use it because what I was looking for was not in Excel’s Help, but is provided by VBA in a file called ‘Visual Basic Language Reference’ and named ‘VBLR6.CHM’. Where is this file? In a standard installation of Office, it is located in the folder called ‘C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033’. In addition, for VBLR6.CHM there is no such list as the Help Context IDs, or I was not able to find it which is far more likely.

I was finally able to resolve the problem because the above mentioned method is capable to open help pages from URL as well, that is, the CHM does not have to be on a local disk.

I am not an expert and have only seen someone else create CHM’s (we outsource this activity). But since a CHM is a compiled help file, I thought it had to have some kind of file or menu structure embedded inside (how can it otherwise organise and create cross-references?) and that it should be possible, knowing the ‘key’, to target a specific page in the CHM file structure and display it. How?

The answer came from investigating the page I was displaying. I have summarised what I did in the following steps.

Step 1 – from the VB-IDE I located the help page related to the information I was looking for, in my example ‘User Defined Formats’ and opened the related help page:

Looking for help in VBIDE

Visual Basic Help

Step 2 – I right clicked on the page and selected ‘Properties‘. The key we need is in the highlighted box:

Page Properties

Step 3 – I selected all the text in the Address (URL) field and copied it to the clipboard.

Address to this page

In my case, the full URL sounds:

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove the first part being ‘mk:@MSITStore:‘ leaving us with:

C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. Note that I omitted the Help Context ID here (which is optional, btw).

Application.Help "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm"

There is still one problem to tackle, and that is the actual path to the file. Depending on your language and installation of Windows and on your version of Office, this path can change. For instance in french we would have (I have highlighted the differences in bold):

C:\Programmes\Fichiers Communs\Microsoft Shared\VBA\VBA6\1036\vblr6.chm::/html/vafmtStringFormats.htm

VBA again is able to help us, offering us these 2 functions:

  • Environ() retrieving settings from the OS, and
  • Application.LanguageSettings.LanguageID() returning the ID of the language.

With this final information at hand we create this chunk of code and VBA is going to show us exactly the page we wanted:

Application.Help VBA.Environ$("commonprogramfiles") & _
                 "\Microsoft Shared\VBA\VBA6\" & _
                 Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                 "\vblr6.chm::/html/vafmtUserDefinedDateFormats.htm"

That’s it. Hook it to a command button or menu and you have your context sensitive help.

Should work with any CHM file (no guarantee though, my name is not Bill G.)


Instructions for Office 2010 users

Office 2010 uses the new VBA7 Visual Basic for Application engine, and I have seen that this one does not really cope correctly with the Application.Help instruction. Instead of opening the given file, I always end up with standard Office Help. But there is a solution that also works on pre-2010 versions of Office, based on a Win32API function.

First we need to load the Win32API function in our project. The declaration is for a 32-bit version of Office

Public Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Any) As Long
Public Const HH_DISPLAY_TOPIC = &H0

The important part in the definition is the last parameter declaration: it use ‘As Any‘ instead of ‘As Long’ what you may find in other posts, which will allow us providing help ID’s as Longs and as Strings, the latter we will use here. The only constant declared (there are others) is used when calling this function to display the desired topic from our help file.

Finally we need to call this function. The next example uses again the instructions explained above for Office XP/2003/2007, but with the necessary differentiation for handling 2010’s new VBA7 engine while supporting legacy VBA6 engine.

The page reference is slightly different from the one for Office XP/2003/2007. You must take the path to the desired page working the HTML structure from the root up. Similar to above, the full URL for 2010 is (note the different folder VBA7 instead of VBA6):

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA7\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove everything up to the double colon including the first backslash:

html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. The next statements include localisation support for retrieving the correct language file.

Dim sCHMHelpFile As String, sPageReference As String
#If VBA7 Then
    ' Office 2010: the help file is in a folder named 'VBA7'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA7</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#Else
    ' Office pre-2010: the help file is in a folder named 'VBA6'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA6</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#End If
sPageReference = "html/vafmtStringFormats.htm"
Call HtmlHelp(0, sCHMHelpFile, HH_DISPLAY_TOPIC, sPageReference)

Have fun.

March 21, 2012

Range from Array – Limits on length of Strings

Filed under: Excel,Programming,VBA — dutchgemini @ 11:05 am
Tags: , , , , , , , ,

In a recent post (click here) I discussed an error I ran into using CopyFromRecordSet. While trying to resolve the problem, I reverted to using the ADODB GetRows method but ended up the same situation: incomplete data set.

Lucky enough, I ran into a strictly related problem: if you write to ranges using arrays, the method will fail if the array contains a string element sized larger than 8203 characters.

Note: CopyFromRecordSet method fails for the same reason and on the same limit.

Paste the above code in a VBA module to see this happen:

Sub RangeFromArrayFails()
On Error Resume Next
    Dim i As Long, a As Variant

    ReDim a(1 To 2, 1 To 1) As Variant     ' Need an array with 2 rows

    For i = 1 To 32767 Step 2              ' 32767 is Excel's cell limit
        a(1, 1) = String(i, "*")           ' Create a string
        a(2, 1) = String(i + 1, "*")       ' As above
        ActiveSheet.Range("A1:A2").Clear   ' clear the area
        ActiveSheet.Range("A1:A2") = a     ' Assign array to range
        If (Err.Number <> 0) Then Exit For ' Error, quit.
    Next i

    Debug.Print IIf(i > 32767, "No limit", "Limit reached at: " & i)
    Debug.Print "Length A1", Len(ActiveSheet.Range("A1"))
    Debug.Print "Length A2", Len(ActiveSheet.Range("A2"))
End Sub

This should be your result:

Limit: 8203
Length A1      8203
Length A2      0

You can try the above code with only 1 array element and see it end without problems.

Have a nice day.

Error -2147467259 Method ‘CopyFromRecordset’ of object ‘Range’ failed

Filed under: Excel,Programming,VBA — dutchgemini @ 10:28 am
Tags: , , , , , , , ,

Recently I ran into error number ‘-2147467259‘ with description “Method ‘CopyFromRecordset’ of object ‘Range’ failed” while transferring a data set from SQL Server into Excel using the aforementioned method on an ADODB.RecordSet.

Of course I Googled for the error but the answers were vague and often pointed to Null values in the RecordSet’s data, but I had other sets that had such empty values and these were dropped without problems, so it had to be somewhere else, in particularly in the data.

The reason for this error is not so easy to find, since CopyFromRecordset may indeed copy data to the worksheet (in my case 31,655 rows of data were copied out of 41,899). From Excel’s point of view, the RecordSet is like a huge array and CopyFromRecordset drops the values by columns and by rows (“Over, then down” in Excel terms). At row 31,655 data was dropped in columns 1 til 4 but no data from column 5 til the last column (31).

After investigation I have been able to isolate the problem and discovered that a very long string in column 5 of the RecordSet —retrieved from a MEMO data type— generated the error. The offending string was 10366 characters long, about a third of Excel’s maximum cell size of 32767 characters. I also discovered that other records having very large text —about 8100 characters—  in the same field in the RecordSet were copied to the worksheet successfully. This made me curious.

I prepared a small test using Excel and Access (2007) and finally was able to establish the upper limit at 8203 character that CopyFromRecordset could handle without problems. Any string length above 8203 characters inevitably fails.

Note: this error is likely generated only when there are at least 2 rows or columns in the RecordSet. A RecordSet with a single value may not even trigger it, or at least I was unable to.

If you have control on the way the RecordSet is generated and you want to use CopyFromRecordset then you should make sure no strings are retrieved large than 8203 characters. If not, then you should revert to use the GetRows method from ADODB and walk your way through the array chopping off any excess characters from the string values before dropping the array to the Range, otherwise this will fail also.

March 19, 2012

Excel VBA Oddity – Replace function not returning entire string

I just boomed into this funny oddity using VBA with Excel.

I wanted to generate data to a text file in the TEMP folder, so I used the FileSystemObject’s GetSpecialFolder() to obtain the TEMP folder name and I crafted a file name using the system time. Then I joined the 2 strings with the backslash character (\).

To avoid problems with double backslash characters, I used Replace() to replace them with single backslash. Since I did not want to replace them at the beginning of the string because I am not sure if the temp folder is not something like for instance in ‘\\server\share\...‘, I decided that I would start replacing from position 3 onwards, like in:

sFileName = VBA.Replace(sFileName, "\\", "\", 3, -1, vbTextCompare)

From Help, I read (and highlight only the relevant parts):

Syntax

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description



start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.

Reading this, I presume that the Replace() statement above should start replacing only from position 3 onwards. Well, it does not. What happens is this:

Replace() replaces ‘find‘ in ‘expression‘ with ‘replace‘, starting at position ‘start‘ but returns a string that does not contain the characters up to ‘start‘.

In other words, it appears that VBA first Mid()‘s the string at position 3, then starts replacing, and finally returns the shortened string. See here as well (includes workaround).

Dutch

PS: if you read Help to the end, it says «If start > Len(expression) then zero-length string is returned». This implicitly confirms that if you want something from the string back using Replace() then you should definitively set start to a value below Len(Expression). And I say that if you want back everything (obviously, with the replacements done), set start to 1.

March 12, 2012

Excel oddity – Embedded UDF is not executed when workbook is opened

The Situation

I have a workbook containing a UDF, let’s call it for convenience MyUDF(), that returns a calculated value. I also have an Add-In (XLA/XLAM) that contains a function with the exact same name. This is not coincidental: the function in the workbook acts as a wrapper for the one in the Add-In.

In the workbook, on one of the sheets I have (these are just a sample):

Cell Function
[A1] =MyUDF("String")
[A2] =MyUDF("String", 0)
[A3] =MyUDF("String", 1, TRUE, FALSE, 200)

In the workbook, MyUDF has 2 parameters:

Public Function MyUDF(ByVal p1 As Variant, ParamArray p2 As Variant) As Variant

The ParamArray p2 As Variant allows me to specify any number of additional parameters to my workbook function, and transforms it into an Array of values (this is what I want). This workbook function finally calls the Add-In equivalent as per:

vResult = Application.Run("MyAddIn.MyUDF", p1, p2)

The UDF in the Add-In has a small but determining difference with the one in the workbook, and that is the number and type of parameter declaration. The function in the Add-In also has 2 parameters but these are defined as:

Public Function MyUDF(ByVal p1 As Variant, Optional ByVal p2 As Variant) As Variant

By design, the Optional ByVal p2 as Variant is processed inside the UDF as an Array of values, which is how it passed by the UDF in the workbook.

In general this works fine, and when I debug I see that control goes first to the workbook UDF and then to the Add-In UDF. So what’s the deal, isn’t Excel designed to do this? Yes, if only…

The Problem

…if only it did not show this when I opened the workbook:

Cell Function Result (sample)
[A1] =MyUDF("String") 25
[A2] =MyUDF("String", 0) 25
[A3] =MyUDF("String", 1, TRUE, FALSE, 200) #VALUE

I was surprised to see the #VALUE but I also saw it rapidly disappear after I refreshed the workbook, so for me it was just a matter of refreshing the data and blamed Excel for it also because I saw sound values in the other cells. Until a customer reported it to me as a bug and I gave it some extra question time to finally discover why it was returning #VALUE and why only for those cells where I used 3 or more parameter values.

This is what I discovered:

  • If you have a workbook containing a UDF, that you have used in your cells and that you have embedded in the VBA code, then Excel will NOT use it when you open the workbook.
  • If you have a module loaded in Excel that provides the namely equal UDF, Excel WILL use that UDF when you open the workbook

  • When you refresh the workbook/worksheets, Excel will use the UDF embedded in the workbook

The Technical Explanation

Using my examples above, in the UDF in the workbook the parameters are a variant value (p1) and a variant array of values (p2). In the UDF in the Add-In the first parameter (p1) is the same but the second parameter (p2) is also a variant value.

When the workbook’s UDF is triggered (situation #3, second image), it forwards the call to the namely equal UDF in the Add-In passing the same number of parameters which do not change, and the Add-In receives a variant value and a variant array of values.

However, if the UDF in the Add-In is called directly (by Excel when opening the workbook—situation #2, first image) then the first parameter is passed as a variant value, the second parameter is passed as a variant value instead of a variant array of values, and from the third parameter onwards they are discarded. This caused the UDF in the Add-In to fail and that was the reason for the #VALUE.

Presumably, when you open a workbook Excel first loads the worksheets and evaluates the cell formulas but it is yet unaware of any UDF in the code modules that the cells may refer to. Only at a later stage these will become active but at that point no recalculation is performed.

The Solution

What lesson have I learned? Well, it’s simple: never trust Excel.

For the rest, make sure the name for a UDF in your workbook’s code modules is unique and cannot be confused with any other [public] name provided by any other module loaded in Excel’s memory (Add-In, XLL, etc.).

In the desperate case you do want the same names and you want to use the UDF in the workbook as a wrapper for a UDF in your Add-In, make sure the parameters match exactly —OR— make sure the either call processes the input parameters correctly.

Dutch

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.

January 2, 2012

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

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 4:08 pm
Tags: , , , ,

The Excel Script for the Add-In

This last page describes the part of the “generic” script that takes care of the installation, update and removal of the Add-In on the target PC. It is also the longest page.

Starting here is indeed the Code section that was not described earlier. This section contain a header with the variable declarations and the constants, the Inno event routines (list 1) and my own custom functions and procedures (list 2). The only ones not put on this page are the support routines which are exposed also on Page 2.

As indicated on Page 2, I have used the following event routines for my customisation:

This is the list of User Defined Routines (UDR) that I have created:

Code Header
[Code]
var
	ActivateProgressPage: TOutputProgressWizardPage;
	DeActivateProgressPage: TOutputProgressWizardPage;
	UninstallProgressForm: TUninstallProgressForm;
	ForMeForAllPage: TInputOptionWizardPage;
	SamplesPathPage: TInputDirWizardPage;
	HTMLHelpPathPage: TInputDirWizardPage;
	LexiconPathPage: TInputDirWizardPage;
	SamplesPath: String;
	HTMLHelpPath: String;
	LexiconPath: String;
	WantsAddIn: Boolean;
	WantsSamples: Boolean;
	WantsHTMLHelp: Boolean;
	WantsLexicon: Boolean;
	InstallMode: Integer;
	ExcelApp: Variant;
	ExcelLibraryPath: String;
	ExcelUserLibraryPath: String;
	ExcelAddInInstalledFailure: Boolean;	// Used for tracing whether Excel was unable to turn
					// the Add-In's '.Installed' property 'True'.
					// This seems to happen installing XLA's on Excel 2007.
					// In that case we try via the Registry.
const
	// These constants are from the XLA and are not known to the install script
	//
	XLA_ADDIN_NAME   = '{#TheAppAddInName}';
	XLAM_FILE_NAME   = '{#TheAppRoot}.XLAM';  // For Excel 2007+
	XLA_FILE_NAME    = '{#TheAppRoot}.XLA';   // For any version of Excel, but in this setup for Excel 2003-
	XLA_HELP_NAME    = '{#TheAppRoot}.CHM';
	XLA_LEXICON_NAME = '{#TheAppRoot}.LEX';
	XLA_FRIENDLYNAME = '{#TheAppShortName}';
InitializeSetup

This routine runs very early so we use it to detect whether Excel is in 32- or in 64-bit (important to know if your Add-In uses components that are not 64-bit, for instance Microsoft Common Controls). It can also used to detect compatibility with third-party applications that the Add-In interfaces to. Failure to comply will abort the installation.

For update installs, the function loads the previous install choices into the global variables.

function InitializeSetup(): Boolean;
(*
	Called during Setup's initialization. Return False to abort Setup, True otherwise.
*)
begin
	// Default.
	//
	Result := true;
	// The Add-In does not (yet) work with 64-bit Excel. We warn and Exit immediately.
	//
	If IsExcel64Bit() then begin
		MsgBox('Oops! Unsupported release of Microsoft Excel (64-bit).' + #13#10 + #13#10 +
			'The {#TheAppName} Add-In is designed to work Microsoft Excel 32-bit only and will' + GetMsgBoxCrLfCharacter +
			'therefore not install. Run this Setup again on a PC with Microsoft Excel 32-bit.' + #13#10 + #13#10 +
			'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
		Result := false;
		Exit;
	end;
	// Load previous choices
	//
	InstallMode := StrToIntDef(GetPreviousData('InstallMode', '0'), 0);
	SamplesPath := GetPreviousData('SamplesPath', GetExpandedConstant('{userdocs}', '{commondocs}', '') + '\My {#TheAppVeryShortName} Projects\Samples');
	HTMLHelpPath := GetPreviousData('HTMLHelpPath', GetExpandedConstant('{sd}', '', '') + '\{#TheAppVeryShortName} HTML Help');
	LexiconPath := GetPreviousData('LexiconPath', GetExpandedConstant('{userdocs}', '{commondocs}', '') + '\My {#TheAppVeryShortName} Lexicons');
end;
InitializeWizard

All custom dialogs are created here, like the “Only for me” and “All Users” selection, the dialogs for selecting a folder for the help and samples and for the Add-In installation progress.

The current script features the «this user or all users» input option page. This makes sense if your application really allows to install per user, but if you plan to bring along also files that are installed in folders accessible to other users –for instance a shared DLL– then you should consider installing for all users only.

procedure InitializeWizard;
begin
	// Create the "For Me/For everyone" page
	//
	ForMeForAllPage := CreateInputOptionPage(wpSelectComponents,
		'Add-In Installation Mode', 'How would you like to install the Add-In?',
		'Please indicate how the Add-In should be installed into Excel, then click Next.',
		true, false);
	// Add item (with an empty caption)
	//
	ForMeForAllPage.Add('Only for me (' + GetUserNameString + ')');
	ForMeForAllPage.Add('For all users of this computer');
	// Set initial value. InstallMode = [0|1]
	//
	ForMeForAllPage.Values[InstallMode] := true;
	// Create the Samples page
	//
	SamplesPathPage := CreateInputDirPage(ForMeForAllPage.ID,
		'Select Samples Data Location', 'Where should the sample data files be stored?',
		'Sample data files will be stored in the following folder.' + #13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'Samples Folder');
	// Add item (with an empty caption)
	//
	SamplesPathPage.Add('');
	// Set initial value (optional)
	//
	SamplesPathPage.Values[0] := RemoveBackslash(SamplesPath);
	// Do the same for the HTML Help page
	//
	HTMLHelpPathPage := CreateInputDirPage(SamplesPathPage.ID,
		'Select HTML Help Location', 'Where should the HTML Help data files be stored?',
		'HTML Help data files will be stored in the following folder.' + #13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'HTML Help Folder');
	HTMLHelpPathPage.Add('');
	HTMLHelpPathPage.Values[0] := RemoveBackslash(HTMLHelpPath);
	// And the Lexicon page
	//
	LexiconPathPage := CreateInputDirPage(HTMLHelpPathPage.ID,
		'Select Lexicon Data Location', 'Where should the lexicon data files be stored?',
		'Lexicon data files will be stored in the following folder.' +#13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'Lexicon Folder');
	LexiconPathPage.Add('');
	LexiconPathPage.Values[0] := RemoveBackslash(LexiconPath);
	// Create progress pages.
	//
	ActivateProgressPage := CreateOutputProgressPage('Activating Add-In',
		'Activating the {#TheAppShortName} Add-In in Microsoft Excel.');
	DeActivateProgressPage := CreateOutputProgressPage('DeActivating Add-In',
		'DeActivating the {#TheAppShortName} Add-In in Microsoft Excel.');
end;
CurStepChanged

Used as a header for calling sub-routines dealing with Pre-Install, Post-Install and Success-Install (all custom routines).

procedure CurStepChanged(CurStep: TSetupStep);
begin
	case CurStep of
	ssInstall:
		DoPreInstall();
	ssPostInstall:
		DoPostInstall();
	ssDone:
		DoSuccessInstall();
	end;
end;
NextButtonClick

This function is used to mark the selected components and will trap whether Excel is running. The script will not allow going beyong the ‘Ready to install’ page if Excel is running.

Also used to memorise the values of the custom dialogs (see also above).

function NextButtonClick(CurPageID: Integer): Boolean;
(*
	Called when the user clicks the Next button. If you return True, the wizard will move to the next page.
	If you return False, it will remain on the current page (specified by CurPageID).
*)
var
	MsgBoxResult: Integer;
begin
	// In principle always continue
	//
	Result := true;
	case CurPageID of
	wpSelectComponents:
		begin
			WantsAddIn    := IsComponentSelected('full\addin');
			WantsSamples  := IsComponentSelected('full\samples');
			WantsHTMLHelp := IsComponentSelected('full\htmlhelp');
			WantsLexicon  := IsComponentSelected('full\lexicon');
		end;
	wpReady:
		begin
			// We are about to start the installation of our components.
			// The installation cannot continue if Excel is running.
			// Return 'False' i.e. 'stop' when Excel is running and we aborted.
			//
			While IsExcelRunning(mbInformation, MB_RETRYCANCEL, IDCANCEL, MsgBoxResult) do begin
				if (MsgBoxResult = IDCANCEL) then begin
					Result := false;
					Exit;
				end;
			end;
		end;
	ForMeForAllPage.ID:
		InstallMode := ForMeForAllPage.SelectedValueIndex;
	SamplesPathPage.ID:
		SamplesPath := SamplesPathPage.Values[0];
	HTMLHelpPathPage.ID:
		HTMLHelpPath := HTMLHelpPathPage.Values[0] ;
	LexiconPathPage.ID:
		LexiconPath := LexiconPathPage.Values[0] ;
	end;
end;
ShouldSkipPage

Detects whether or not to show certain custom or standard dialogs. For instance, if the user does not want to install samples, the page asking for the samples destination folder will be skipped.

function ShouldSkipPage(PageID: Integer): Boolean;
begin
	// In principle always continue (i.e. never skip).
	//
	Result := false;
	case PageID of
	ForMeForAllPage.ID:
		Result := Not WantsAddIn;
	SamplesPathPage.ID:
		Result := Not WantsSamples;
	HTMLHelpPathPage.ID:
		Result := Not WantsHTMLHelp;
	LexiconPathPage.ID:
		Result := Not WantsLexicon;
	end;
end;
UpdateReadyMemo

Prepares the report of what the installer is about to do, including settings from our custom dialogs.

function UpdateReadyMemo(Space, NewLine, MemoUserInfoInfo, MemoDirInfo, MemoTypeInfo, MemoComponentsInfo, MemoGroupInfo, MemoTasksInfo: String): String;
var
	MemoString: String;
begin
	// Standard stuff.
	//
	if (MemoUserInfoInfo <> '')   then MemoString := MemoUserInfoInfo;
	if (MemoDirInfo <> '')        then MemoString := MemoString + NewLine + NewLine + MemoDirInfo;
	if (MemoTypeInfo <> '')       then MemoString := MemoString + NewLine + NewLine + MemoTypeInfo;
	if (MemoComponentsInfo <> '') then MemoString := MemoString + NewLine + NewLine + MemoComponentsInfo;
	if (MemoGroupInfo <> '')      then MemoString := MemoString + NewLine + NewLine + MemoGroupInfo;
	if (MemoTasksInfo <> '')      then MemoString := MemoString + NewLine + NewLine + MemoTasksInfo;
	// Specific stuff.
	//
	if WantsAddIn then begin
		if (InstallMode = 0) then begin
			MemoString := MemoString + NewLine + NewLine + 'Add-In installation mode:' + NewLine + Space + 'Only for me (' + GetUserNameString + ')';
		end else begin
			MemoString := MemoString + NewLine + NewLine + 'Add-In installation mode:' + NewLine + Space + 'For all users of this computer';
		end;
	end;
	if WantsSamples  then MemoString := MemoString + NewLine + NewLine + 'Samples folder:' + NewLine + Space + SamplesPath;
	if WantsHTMLHelp then MemoString := MemoString + NewLine + NewLine + 'HTML Help folder:' + NewLine + Space + HTMLHelpPath;
	if WantsLexicon  then MemoString := MemoString + NewLine + NewLine + 'Lexicon folder:' + NewLine + Space + LexiconPath;
	Result := MemoString;
end;
RegisterPreviousData

Stores the user’s selection of the custom dialogs.

procedure RegisterPreviousData(PreviousDataKey: Integer);
begin
	// Store the settings so we can restore them next time
	//
	SetPreviousData(PreviousDataKey, 'InstallMode', IntToStr(InstallMode));
	SetPreviousData(PreviousDataKey, 'SamplesPath',  SamplesPath);
	SetPreviousData(PreviousDataKey, 'HTMLHelpPath', HTMLHelpPath);
	SetPreviousData(PreviousDataKey, 'LexiconPath',  LexiconPath);
end;
InitializeUninstall

This routine runs very early in the unistallation process. Here only used to trap whether Excel is running or not and abort the uninstallation if the user decides to keep Excel alive.

function InitializeUninstall(): Boolean;
(*
	Called during Uninstall. Return false to abort Setup, True otherwise.
*)
var
	MsgBoxResult: Integer;
begin
	// Default.
	//
	Result := true;
	// We are about to start the uninstallation of our components.
	// The uninstallation cannot continue if Excel is running.
	// Return 'False' i.e. 'stop' when Excel is running and we abort.
	//
	While IsExcelRunning(mbInformation, MB_RETRYCANCEL, IDCANCEL, MsgBoxResult) do begin
		if (MsgBoxResult = IDCANCEL) then begin
			Result := false;
			Exit;
		end;
	end;
end;
InitializeUninstallProgressForm

This particular event routine is used to create a “custom” uninstall progress form, as InnoSetup does not provide access to normal custom progress forms which are available only during installation.

procedure InitializeUninstallProgressForm;
begin
	// We are about to uninstall. To be able to use the UninstallProgressForm during our Uninstall run we need to
	// instantiate it although according to InnoSetup Help (and some Internet sources) it should already exist.
	//
	UninstallProgressForm := TUninstallProgressForm.Create(NIL);
	UninstallProgressForm.Caption := '{#TheAppName} Uninstall';
end;
CurUninstallStepChanged

Like CurStepChanged, used as a header for calling sub-routines dealing with Pre-UnInstall and Post-UnInstall (all custom routines).

procedure CurUninstallStepChanged(CurUninstallStep: TUninstallStep);
begin
	case CurUninstallStep of
	usUninstall:
		DoPreUnInstall();
	usPostUninstall:
		DoPostUnInstall();
	end;
end;
GetSamplesPath

Returns the name of the folder for the samples, properly formatted.

function GetSamplesPath(S: String): String;
(*
	Return the selected 'Samples' directory
*)
begin
	Result := AddBackslash(SamplesPath);
end;
GetHTMLHelpPath

Returns the name of the folder for the HTML Help files, properly formatted.

function GetHTMLHelpPath(S: String): String;
(*
	Return the selected HTML Help folder
*)
begin
	Result := AddBackslash(HTMLHelpPath);
end;
GetLexiconPath

Returns the name of the folder for the Lexicons, properly formatted. In my projects a Lexicon is an ASCII text file containing localised versions of the strings and captions used by the Add-In, which is designed to be language independent. In this way I can easily translate the Add-In.

function GetLexiconPath(S: String): String;
(*
	Return the selected Lexicon folder
*)
begin
	Result := AddBackslash(LexiconPath);
end;
CreateExcelApp

This routine actually starts Excel as an automation server. When it succeeds, it initialises the global variables (declared in the Code Header) containing the folder names where the Add-In can be copied to. Returns ‘true’ when Excel can be initialised as automation server.

function CreateExcelApp: Boolean;
(*
	Create an Excel COM Automation object.
	This COM object is needed for accessing the Add-Ins components of Excel.
*)
begin
	// Default.
	//
	Result := true;

	// Create an Excel COM Automation object.
	//
	try
		ExcelApp := CreateOleObject('Excel.Application');
	except
		if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('Could not initialise Excel.Application.');
	end;

	if VarIsEmpty(ExcelApp) then begin
		if IsRunningSilent then begin
			Log('Could not start Microsoft Excel. Contact a System Administrator.');
		end else begin
			MsgBox('Oops! Could not activate Microsoft Excel.' + #13#10 + #13#10 +
				'The {#TheAppName} is a Microsoft Excel Add-In and requires'+ GetMsgBoxCrLfCharacter +
				'Microsoft Excel. This procedure could not find Microsoft Excel. The Add-In'+ GetMsgBoxCrLfCharacter +
				'will therefore not install.' + #13#10 + #13#10 +
				'If this notification is unexpected, contact a System Administrator.', mbInformation, mb_Ok);
		end;
		Result := false;
	end else begin
		// Excel started. On systems with dual monitors, Excel sometimes starts with a visible frame so hide.
		//
		ExcelApp.Visible := false;

		// Load the default Add-Ins folder (this is a Global Variable).
		//
		ExcelLibraryPath := ExcelApp.LibraryPath;
		ExcelUserLibraryPath := ExcelApp.UserLibraryPath;

		// According to M$, we must add an empty workbook in order to access the Add-In Manager using OLE Automation.
		// Excel start without a workbook in this situation. The workbook will be dropped anyway (see below). With
		// Excel 2007+ you can access the Add-Ins Manager also without an open workbook, but who cares...
		//
		try
			ExcelApp.Workbooks.Add
		except
		end;
	end;
end;
KillExcelApp

This routine stops Excel as an automation server. Does not provide feedback in case of trouble.

procedure KillExcelApp();
(*
	Shut down the Excel COM Automation object.
	This COM object was needed for accessing the Add-Ins components of Excel.
*)
var
	ExceptionRaised: Boolean;
begin
	if not VarIsEmpty(ExcelApp) then begin
		// All statements needed by Excel to close down without leaving garbage.
		// Suppress whatever warning Excel may cough up.
		//
		ExcelApp.DisplayAlerts := false;

		// Loop all open workbooks. If for whatever reason we run into
		// trouble, we use the ExceptionRaised to stop closing workbooks.
		//
		ExceptionRaised := false;
		while (ExcelApp.Workbooks.Count > 0) and not ExceptionRaised do begin
			// Close the workbook. Turn the workbook 'clean' by
			// setting the '.Saved' property to 'true'.
			//
			try
				ExcelApp.Workbooks(1).Saved := true;
			except
			end;

			// Now close the workbook. On Excel 2000 (others not verified) the '.Close'
			// statement may fail as you are not allowed to close the last open workbook.
			//
			try
				ExcelApp.Workbooks(1).Close;
			except
				ExceptionRaised := true;
			end;
		end;

		// Kill Excel.
		//
		try
			ExcelApp.Quit;
		except
		end;
	end;

	// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
	//
	ExcelApp := Unassigned;
end;
HookAddinToExcel

This function hooks the Add-In into Excel. To make the activation visually more appealing we use a custom progress page with 4 steps:

  1. start of Excel
  2. loading of the Add-In in the Add-In space
  3. activation of the Add-In
  4. stopping of Excel

A fifth position is used for obtaining the 100% progress bar. You can uncomment the Sleep(200) statement if you want to add a small delay between the various steps and give the impression that each step is actually taking some CPU (otherwise the dialog may just flash by).

On Windows Vista, and this is even more visible on Windows 7, the progress bar on the custom page ‘runs behind’ the actual progress position, something which does not occur with progress bar on Inno’s standard pages (or on XP). The effect is that the various steps are properly displayed but the progress bar remains visually stuck at 0%. The progress bar does not have a ‘refresh’ method to force a refresh via code before continuing.

 I tried to use the Sleep(200) statement with various delays to avoid this effect but without success. Apparently the control used to paint the progress bar runs in a separate thread and Windows is too busy adding fancy effects that it can’t even finish painting the bar before the Inno code kills the dialog.

The function returns a String containing the full name of the Add-In.

function HookAddinToExcel(): String;
(*
	Hooks the Add-In to Excel. Hooking the Add-In means loading the Add-In
	in Excel's Add-Ins space and setting the '.Installed' property to 'True'.
*)
var
	ExcelAddIn: Variant;
	OpenCounter: Integer;
	RegistryKey: String;
	RegistryValue: String;
	RegistryData: String;
begin
	// The return value is the full name of the Add-In.
	//
	Result := '';

	try
		begin
			Log('Starting Microsoft Excel COM Automation.');

			if IsInstaller then begin
				ActivateProgressPage.Show;
				ActivateProgressPage.SetText('Starting Microsoft Excel COM Automation to enable Add-In...', '');
				ActivateProgressPage.SetProgress(0,4);
				// Sleep(200);
			end;

			// Get, or create which is much more likely, an Excel COM Automation object
			//
			if CreateExcelApp then begin
				Log('Loading the Add-In to enable.');

				if IsInstaller then begin
					ActivateProgressPage.SetText('Loading the Add-In...', '');
					ActivateProgressPage.SetProgress(1,4);
					// Sleep(200);
				end;

				// Get the Add-In. If it's not there, add. The file was already copied to the correct folder. Excel seems
				// to do that anyway when the drive where the source file is located is a network/removable drive.
				//
				try
					ExcelAddIn := ExcelApp.AddIns(XLA_ADDIN_NAME);
				except
					if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('No matching Add-In found, adding Add-In.');
					ExcelAddIn := ExcelApp.AddIns.Add(XLA_FILE_NAME, true);
				end;

				if VarIsEmpty(ExcelAddIn) then begin
					if IsRunningSilent then begin
						Log('Could not enable Add-In. Add-In not loaded. If this error is unexpected, contact a System Administrator.');
					end else begin
						MsgBox('Error: Could not enable Add-In. Add-In not loaded.' + #13#10 + #13#10 +
							'If this error is unexpected, contact a System Administrator.', mbError, mb_Ok);
					end;
				end else begin
					Log('Enabling the Add-In.');

					if IsInstaller then begin
						ActivateProgressPage.SetText('Enabling the Add-In...', '');
						ActivateProgressPage.SetProgress(2,4);
						// Sleep(200);
					end;

					// Propagate the name of the Add-In to the Caller.
					//
					Result := ExcelAddIn.FullName;

					// Try to enable the Add-In via Excel.
					//
					ExcelAddInInstalledFailure := false;
					try
						ExcelAddIn.Installed := true;
					except
						// Here we are, failure loading the Add-In.
						//
						ExcelAddInInstalledFailure := true;
					end;

					// On some combinations of OS & Excel (especially on Vista and Windows 7), working
					// with the '.Installed' property makes Excel re-appear again which is rather annoying.
					//
					ExcelApp.Visible := false;

					if ExcelAddInInstalledFailure then begin
						// We had a failure installing the Add-In via Excel.
						//
						if IsRunningSilent then begin
							Log('Could not enable Add-In. Now enabling via Registry. If this error is unexpected, contact a System Administrator.');
						end;

						// We now try to do this via the Registry, but must be sure that Excel is not running.
						// first we read the name of the Add-In. 'InstallMode = 0' means 'For me only'. Only the
						// root name of the Add-In if installed as 'For me only' and the full path if installed
						// as 'For all users'. Must be enclosed in double quotes, otherwise Excel doesn't like it.
						//
						if (InstallMode = 0) then begin
							RegistryData := '"' + ExcelAddIn.Name + '"';
						end else begin
							RegistryData := '"' + ExcelAddIn.FullName + '"';
						end;

						// Close Excel. Must do so otherwise the next settings are voided.
						//
						KillExcelApp;

						// To install an Add-In via the Registry, write a REG_SZ value called "OPENx"
						// to the "HKCU\Software\Microsoft\Office\\Excel\Options" key.
						//
						RegistryKey := 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Options';
						OpenCounter := 0;
						while (OpenCounter >= 0) do begin
							// The "OPENx" value is "OPEN" for the first Add-In
							// and "OPEN1", "OPEN2", "OPEN..." for the next ones.
							//
							if (OpenCounter = 0) then begin
								RegistryValue := 'OPEN';
							end else begin
								RegistryValue := 'OPEN' + IntToStr(OpenCounter);
							end;

							// If the value exists then it pertains to another Add-In.
							// If missing, we add the value because this is our baby.
							//
							if not RegValueExists(HKEY_CURRENT_USER, RegistryKey, RegistryValue) then begin
								Log('Writing Registry entry: ' + RegistryKey + ', ' + RegistryValue + ', ' + RegistryData);

								RegWriteStringValue(HKEY_CURRENT_USER, RegistryKey, RegistryValue, RegistryData);

								// Stop here.
								//
								OpenCounter := -1;
							end else begin
								// Check next.
								//
								OpenCounter := OpenCounter + 1;
							end;
						end;
					end;
				end;
			end;

			// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
			//
			Log('Stopping Microsoft Excel COM Automation after enabling Add-In.');

			if IsInstaller then begin
				ActivateProgressPage.SetText('Stopping Microsoft Excel COM Automation...', '');
				ActivateProgressPage.SetProgress(3,4);
				// Sleep(200);
			end;

			KillExcelApp;

			if IsInstaller then begin
				ActivateProgressPage.SetProgress(4,4);
				// Sleep(500);
			end;
		end;
	finally
		ActivateProgressPage.Hide;
	end;

	// Don't need this instance anymore.
	//
	ExcelAddIn := Unassigned;
end;
UnhookAddinFromExcel

This function unhooks the Add-In from Excel. To make the deactivation visually more appealing we use a progress page with 4 steps:

  1. start of Excel
  2. connecting to the Add-In in the Add-In space
  3. deactivation of the Add-In
  4. stopping of Excel.

A fifth position is used for obtaining the 100% progress bar. See HookAddinToExcel for additional information on the UI issues.

During uninstallation we cannot use custom progress pages as they cannot be created (the atoms are not available in the uninstall stub). That why we use the internal uninstall progress form and code accordingly since it has a slightly different interface.

The function returns a String containing the full name of the Add-In which can be used later on to physically remove the file from the hard disk.

function UnhookAddinFromExcel(): String;
(*
	UnHooks the Add-In from Excel. UnHooking the Add-In means setting the '.Installed'
	property to 'False'. No need to remove the Add-In from Excel's Add-Ins space.
*)
var
	ExcelAddIn: Variant;
begin
	// The return value is the full name of the Add-In.
	//
	Result := '';

	try
		begin
			Log('Starting Microsoft Excel COM Automation to disable Add-In.');

			if IsInstaller then begin
				// Setup (i.e. install) can use custom forms because these can be [and are] initialised.
				//
				DeActivateProgressPage.Show;
				DeActivateProgressPage.SetText('Starting Microsoft Excel COM Automation...', '');
				DeActivateProgressPage.SetProgress(0,4);
			end else begin
				// Uninstall cannot use custom forms because they cannot be initialised. For this we shall be
				// able to use the native UninstallProgressForm of type TUninstallProgressForm.
				//
				UninstallProgressForm.Show
				UninstallProgressForm.PageNameLabel.Caption := 'DeActivating Add-In';
				UninstallProgressForm.PageDescriptionLabel.Caption := 'DeActivating the {#TheAppShortName} Add-In in Microsoft Excel.';
				UninstallProgressForm.StatusLabel.Caption := 'Starting Microsoft Excel COM Automation...';
				UninstallProgressForm.ProgressBar.Min := 0;
				UninstallProgressForm.ProgressBar.Max := 4;
				UninstallProgressForm.ProgressBar.Position := 0;
			end;
			// Sleep(200);

			// Create an Excel COM Automation object.
			//
			if CreateExcelApp then begin
				Log('Retrieving the Add-In to disable.');

				if IsInstaller then begin
					DeActivateProgressPage.SetText('Retrieving the Add-In...', '');
					DeActivateProgressPage.SetProgress(1,4);
				end else begin
					UninstallProgressForm.StatusLabel.Caption := 'Retrieving the Add-In...';
					UninstallProgressForm.ProgressBar.Position := 1;
				end;
				// Sleep(200);

				// Get the Add-In.
				//
				try
					ExcelAddIn := ExcelApp.AddIns(XLA_ADDIN_NAME);
				except
					if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('No matching Add-In found.');
				end;

				if not VarIsEmpty(ExcelAddIn) then begin
					Log('Deactivating the Add-In.');

					if IsInstaller then begin
						DeActivateProgressPage.SetText('Deactivating the Add-In...', '');
						DeActivateProgressPage.SetProgress(2,4);
					end else begin
						UninstallProgressForm.StatusLabel.Caption := 'Deactivating the Add-In...';
						UninstallProgressForm.ProgressBar.Position := 2;
					end;
					// Sleep(200);

					Result := ExcelAddIn.FullName;
					try
						ExcelAddIn.Installed := false;
					except
					end;
					ExcelApp.Visible := false;
				end;
			end;

			// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
			//
			Log('Stopping Microsoft Excel COM Automation after disabling Add-In.');

			if IsInstaller then begin
				DeActivateProgressPage.SetText('Stopping Microsoft Excel COM Automation...', '');
				DeActivateProgressPage.SetProgress(3,4);
			end else begin
				UninstallProgressForm.StatusLabel.Caption := 'Stopping Microsoft Excel COM Automation...';
				UninstallProgressForm.ProgressBar.Position := 3;
			end;
			// Sleep(200);

			KillExcelApp;

			if IsInstaller then begin
				DeActivateProgressPage.SetProgress(4,4);
			end else begin
				UninstallProgressForm.ProgressBar.Position := 4;
			end;
			// Sleep(500);
		end;
	finally
		if IsInstaller then begin
			DeActivateProgressPage.Hide;
		end else begin
			UninstallProgressForm.Hide;
		end;
	end;
end;
CopyTheAddIn

This functions extracts the XLA and XLAM if available to a temporary folder and then copies the appropriate one to the correct Excel Add-In folder. The script also copies a .CHM file with the on-line help to the same folder as the Add-In.

A string variable called FullErrorString defined as a local variable will contain errors that this function may encounter during execution. This string is logged to screen or to file.

The function returns a String containing the full name of the Add-In.

function CopyTheAddIn(): String;
(*
	Copies the Add-In and its accessory files to the destination folder.
*)
var
	SourcePath: String;
	ExcelAddInPath: String;
	CopySuccess: Boolean;
	FullErrorString: String;
begin
	// Default.
	//
	Result := '';

	// The Add-In is not copied by the Installer (Flags: dontcopy;) so it need to be
	// extracted from the package before we can copy it to the destination folder.
	// The 'ExtractTemporaryFile' may hickup an exception which is ignored here.
	//
	// Extract the new macro-enabled 'Zipped-XML' XLAM from the package if available.
	//
	try
		ExtractTemporaryFile(XLAM_FILE_NAME);
	except
	end;
	//
	// Extract the old-style XLA from the package.
	//
	try
		ExtractTemporaryFile(XLA_FILE_NAME);
	except
	end;

	// We copy the Add-In files(s) to the selected Add-Ins folder. Fetch the
	// appropriate folder. If the variable is still uninitialised, create it.
	//
	if (InstallMode = 0) then begin
		if (ExcelUserLibraryPath = '') then CreateExcelApp;
		ExcelAddInPath := ExcelUserLibraryPath;
	end else begin
		if (ExcelLibraryPath = '') then CreateExcelApp;
		ExcelAddInPath := ExcelLibraryPath;
	end;
	ExcelAddInPath := AddBackslash(ExcelAddInPath);

	if (ExcelAddInPath <> '') then begin
		// Look if the directory exists and if not, create the entire path to this directory.
		//
		if not DirExists(ExcelAddInPath) then ForceDirectories(ExcelAddInPath);

		// Copy the file(s). The first set of files is located in the temporary folder.
		//
		SourcePath := AddBackslash(GetExpandedConstant('{tmp}', '', ''));
		CopySuccess := false;
		FullErrorString := '';
		if IsExcel2007OrHigher and FileExists(SourcePath + XLAM_FILE_NAME) then begin
			// Excel 2007+ with XLAM
			//
			if FileExists(SourcePath + XLAM_FILE_NAME) then begin
				CopySuccess := FileCopy(SourcePath + XLAM_FILE_NAME, ExcelAddInPath + XLAM_FILE_NAME, false);
				if not CopySuccess then begin
					// See if there are any errors.
					//
					if (DLLGetLastError <> 0) then begin
						FullErrorString := AddPeriod(SysErrorMessage(DLLGetLastError));
					end else begin
						FullErrorString := 'Unknown error during copy of ''' +
										   SourcePath + XLAM_FILE_NAME +
										   ''' to ''' +
										   ExcelAddInPath + XLAM_FILE_NAME + '''.';
					end;
				end;
			end;
		end else begin
			// Excel 2003- or XLA only.
			//
			if FileExists(SourcePath + XLA_FILE_NAME) then begin
				CopySuccess := FileCopy(SourcePath + XLA_FILE_NAME, ExcelAddInPath + XLA_FILE_NAME, false);
				if not CopySuccess then begin
					// See if there are any errors.
					//
					if (DLLGetLastError <> 0) then begin
						FullErrorString := AddPeriod(SysErrorMessage(DLLGetLastError));
					end else begin
						FullErrorString := 'Unknown error during copy of ''' +
										   SourcePath + XLA_FILE_NAME +
										   ''' to ''' +
										   ExcelAddInPath + XLA_FILE_NAME + '''.';
					end;
				end;
			end;
		end;

		// The next set of files is located in the '{app}' folder.
		//
		SourcePath := AddBackslash(GetExpandedConstant('{app}', '', ''));
		if FileExists(SourcePath + XLA_HELP_NAME) then begin
			CopySuccess := FileCopy(SourcePath + XLA_HELP_NAME, ExcelAddInPath + XLA_HELP_NAME, false) and CopySuccess;
			if not CopySuccess then begin
				// See if there are any errors.
				//
				if (Length(Trim(FullErrorString)) > 1) then FullErrorString := FullErrorString + #13#10;
				if (DLLGetLastError <> 0) then begin
					FullErrorString := FullErrorString +
									   AddPeriod(SysErrorMessage(DLLGetLastError));
				end else begin
					FullErrorString := FullErrorString +
									   'Unknown error during copy of ''' +
									   SourcePath + XLA_HELP_NAME +
									   ''' to ''' +
									   ExcelAddInPath + XLA_HELP_NAME + '''.';
				end;
			end;
		end;

		// Return where we dropped the file(s).
		//
		if CopySuccess then begin
			if IsExcel2007OrHigher and FileExists(ExcelAddInPath + XLAM_FILE_NAME) then begin
				// Excel 2007+ with XLAM
				//
				Result := (ExcelAddInPath + XLAM_FILE_NAME);
			end else begin
				// Excel 2003- or XLA only
				//
				Result := (ExcelAddInPath + XLA_FILE_NAME);
			end;
		end else begin
			if IsRunningSilent then begin
				Log('Could not copy Add-In or one of its support files to ''' + ExcelAddInPath + '''. If this error is unexpected, contact a System Administrator.');
			end else begin
				if (Length(Trim(FullErrorString)) > 1) then FullErrorString := FullErrorString + #13#10 + #13#10;
				MsgBox('Error: could not copy Add-In or one of its support files to ''' + ExcelAddInPath + '''.' + #13#10 + #13#10 +
					FullErrorString + 'If this error is unexpected, contact a System Administrator for assistance.', mbError, mb_Ok);
			end;
		end;
	end else begin
		// Oops, empty folder? This should not happen but if it does, then warn.
		//
		if IsRunningSilent then begin
			Log('Could not establish Add-In path. If this error is unexpected, contact a System Administrator.');
		end else begin
			MsgBox('Error: could not establish Add-In path.' + #13#10 + #13#10 +
				'If this error is unexpected, contact a System Administrator for assistance.', mbError, mb_Ok);
		end;
	end;

	// If we started Excel then we don't need this instance anymore.
	// It will also forcibly terminate the 'EXCEL.EXE' process.
	//
	KillExcelApp;
end;
RemoveTheAddIn

This function physically removes the Add-In from Excel’s folders and will return True if successful. It receives the full name of the Add-In from the function caller. This is the name returned by for instance UnhookAddinFromExcel. A localised version of the lexicon file found in the Excel folder is also removed (this one would have been copied by the Add-In).

function RemoveTheAddIn(ExcelAddInFullName: String): Boolean;
(*
	Remove the Add-In and its accessory files from the destination folder
*)
var
	ExcelAddInPath: String;
begin
	Result := true;

	// We remove the files(s) from the same folder as the Add-In.
	//
	if (ExcelAddInFullName <> '') then begin
		// Main Add-In.
		//
		if FileExists(ExcelAddInFullName) then Result := DeleteFile(ExcelAddInFullName) and Result;

		// Other related files.
		//
		ExcelAddInPath := AddBackslash(ExtractFileDir(ExcelAddInFullName));
		if FileExists(ExcelAddInPath + XLA_HELP_NAME)    then Result := DeleteFile(ExcelAddInPath + XLA_HELP_NAME)    and Result;
		if FileExists(ExcelAddInPath + XLA_LEXICON_NAME) then Result := DeleteFile(ExcelAddInPath + XLA_LEXICON_NAME) and Result;

		// Registry. If the key is located under "Add-in Manager" then this is a non-active Add-In in a non-standard folder on
		// the same drive as where Excel is installed. Non-active Add-Ins in standard folders do not appear in the registry but
		// are read by Excel directly from such folders.
		//
		if RegValueExists(HKEY_CURRENT_USER, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Add-in Manager', ExcelAddInFullName) then begin
			RegDeleteValue(HKEY_CURRENT_USER, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Add-in Manager', ExcelAddInFullName)
		end;
	end;
end;
DoPreInstall

This routine runs before perfoming any installation activity. This is the right place to perform a test on the environment and this is exactly what we will do here.

At the moment our test is limited to checking the correct version of VBE6.DLL, since I’ve found that below a particular version of VBE6.DLL Excel crashes using my Add-In (you can read this post on the subject). You can add code to quit the installer when the check returns False but I chose not to.

procedure DoPreInstall();
(*
	Pre-Installation procedure. Runs before performing any action.
*)
begin
	// We are about to install. Check if VBE6.DLL can cause any problem.
	//
	CheckVBE6DLLVersion();
end;
DoPostInstall

This procedure runs after having finished installing all of the regular stuff, which excludes copying the XLA/XLAM and related to the Add-Ins folder of Excel in reason of the dontcopy flag.

At this point we can proceed and remove the Add-In currently installed and place the one in this package into the correct folder. Should this be the first time that the setup is executed, then the unhook function will return an empty string and the file removal will be skipped. I have deliberately chosen not to use Boolean functions because using String functions allows me to carry around the full name of the Add-In without using global variables.

procedure DoPostInstall();
(*
	Post-Installation procedure. Runs after performing all installation actions.
*)
var
	ExcelAddInFullName: String;
begin
	// We have finished installing so we need to hook the Add-In into Excel and activate/enable it.
	// We first remove the old Add-In from Excel and the Registry and from the installation folder also
	// otherwise Excel continues to 'see' it in its Add-Ins dialog.
	//
	ExcelAddInFullName := UnhookAddinFromExcel();

	// Remove the Add-In if found between the Excel Add-Ins.
	//
	if (ExcelAddInFullName <> '') then RemoveTheAddIn(ExcelAddInFullName);

	// Copy the new Add-In.
	//
	ExcelAddInFullName := CopyTheAddIn();

	// Hook the new Add-In into Excel. Error messages trapped above.
	//
	if (ExcelAddInFullName <> '') then HookAddinToExcel();
end;
DoSuccessInstall

This procedure runs after the installation was successful. I use this routine to check the Trust Settings on the target PC. The active part in this code only warns or logs if the settings are not properly set, while the commented part actually would set them as required for the Add-In to run smoothly.

The Trust Settings are only tested at this stage and not earlier because I found it more logical to do it here.

procedure DoSuccessInstall();
(*
	Successful Installation procedure. Runs after performing all actions.
*)
var
	TrustAddInsAndTemplates: Boolean;
	TrustVBProject: Boolean;
	MsgBoxResult: Integer;
begin
	// Check Trust to Add-Ins and Templates (for all versions)
	//
	TrustAddInsAndTemplates := IsTrustAddInsAndTemplatesChecked;

	// Check in Access To Visual Basic Project is enabled in Excel.
	// Note: Excel 2000 does not have this setting.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		TrustVBProject := IsTrustVBProjectChecked;
	end else begin
		TrustVBProject := true;
	end;

	// If one of the 2 is not set, then issue a warning message.
	//
	if not TrustAddInsAndTemplates or not TrustVBProject then begin
		if IsRunningSilent then begin
			Log('Warning: Macro Security Trust settings not properly set. {#TheAppName} may not work properly.');
		end else begin
			// Just a message.
			// Comment this statement and uncomment next statement if you want to set.
			//
			MsgBoxResult := MsgBox('Information: Macro Security Trust settings not properly set.' + #13#10 + #13#10 +
				'To operate with {#TheAppName}, the Trust settings in the Macro Security must be set differently.' + #13#10 + #13#10 +
				'Contact a System Administrator for assistance.', mbInformation, mb_Ok);

//			// A question whether this shall be turned on right now?
//			// Uncomment this statement and comment previous statement if you want to set.
//			//
//			MsgBoxResult := MsgBox('Information: Macro Security Trust settings not properly set.' + #13#10 + #13#10 +
//				'To operate with {#TheAppName}, the Trust settings in the Macro Security must be set differently.' + #13#10 + #13#10 +
//				'Would you like to do this right now?', mbConfirmation, mb_YesNo);
//			//
//			if (MsgBoxResult = IDYES) then begin
//				if not SetTrustAddInsAndTemplates and not SetTrustVBProject then begin
//					MsgBox('Warning: could not set required Macro Security Trust settings.' + #13#10 + #13#10 +
//						'You may not have sufficient privileges to perform this action. Before you can operate with' + #13#10 +
//						'{#TheAppName}, remember to check and/or set these setting in Microsoft Excel.' + #13#10 + #13#10 +
//						'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
//				end;
//			end;
		end;
	end;
end;
DoPreUnInstall

This procedure runs before the uninstallation begins. Currently it does nothing and is included for symmetry.

procedure DoPreUnInstall();
(*
	Pre-UnInstallation procedure. Runs before performing any action.
*)
begin
	// Here we do nothing at all.
	//
end;
DoPostUnInstall

This procedure runs after the uninstallation has succeeded, and is designed to perform ‘garbage collection’ and remove files not managed by Inno.

procedure DoPostUnInstall();
(*
	Post-UnInstallation procedure. Runs after performing all uninstallation actions.
*)
var
	ExcelAddInFullName: String;
begin
	// We have finished uninstalling so we need to remove the Add-In from the Registry and the
	// installation folder also otherwise Excel continues to 'see' it in its Add-Ins dialog.
	//
	ExcelAddInFullName := UnhookAddinFromExcel();

	// Remove the Add-In if found between the Excel Add-Ins.
	//
	if (ExcelAddInFullName <> '') then RemoveTheAddIn(ExcelAddInFullName);
end;

Assembling the bits and pieces of these pages you will be able to create your own Installer for Excel Add-Ins.

I hope you like what you’ve read and hope it can help you with your work. Since this contribution, and the other ones on this blog, have cost me time and money, you can show your appreciation by making a donation.

Previous Page

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

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 12:45 pm
Tags: , , , ,

The Script specific for the Add-In

This is the main script that deals with the Add-In specific data and that sets some variable data used in the “Excel” generic script called with an #include statement (see at the end of this page).

Definitions:

; Definitions
;
; NOTE: The value of AppId uniquely identifies this application.
; Do not use the same AppId value in installers for other applications.
; (To generate a new GUID, click Tools | Generate GUID inside the IDE.)
;
#define TheAppId "{11111111-2222-3333-4444-555555555555}"
#define TheAppName "The Dutch Gemini Add-In for Excel"
#define TheAppShortName "Dutch Gemini"
#define TheAppVeryShortName "DGXLAddIn"
#define TheAppVersion "1.2.3"
#define TheAppAddInName "DUTCH ADD-IN"
#define TheAppRoot "DGXLAI"
#define TheSetupRoot "DG_AI"
#define TheSetupVersion StringChange(TheAppVersion, ".", "_")
;
; Increase the number at the end for every new SETUP of the same {#TheAppVersion}.
; Restart the number with "1" for every new RELEASE of {#TheAppVersion}.
; This is version '1' of the setup, based on the same component.
;
#define TheSetupVersionBuild "1"
#define TheCopyright "Copyright © 2011-" + GetDateTimeString("yyyy", "", "")
#define TheCompany "Excel Development by Dutch Gemini"
#define TheContact "Dutch Gemini"
#define TheURL <a href="https://dutchgemini.wordpress.com">https://dutchgemini.wordpress.com</a>

I have added the following instruction for conditional compilation of items under Components and Files. I have no clue about the specific syntax of this statement, but it works so I do not bother either.

;
; For conditional compilation of [Components] and [Files]
;
#define WildcardExists(str Filename) \
  ((Local[0] = FindFirst(Filename, faAnyFile)) ? \
   (FindClose(Local[0]), True) : False)
Setup section

Here begins the Setup section. Most of the items use definitions declared above.

[Setup]
;
; Put the date on December 31, 2011 at 1.2.0
;
TouchDate=2011-12-31
TouchTime=01:02:00
;
; Establish install logging. If set to "true" then always log. Override at run-time
; with setup command line option "/LOG" (to %TEMP%) or "/LOG=filename".
;
SetupLogging=false
MinVersion=0,5.0.2195sp3
;
AppId={{#TheAppId}
;
AppComments={#TheAppName}
AppContact={#TheContact}
AppCopyright={#TheCopyright} {#TheCompany}
AppName={#TheAppName}
AppPublisher={#TheCompany}
AppVerName={#TheAppName} {#TheAppVersion}
AppVersion={#TheAppVersion}
;
AppPublisherURL={#TheURL}
AppSupportURL={#TheURL}
AppUpdatesURL={#TheURL}
;
VersionInfoCompany={#TheCompany}
VersionInfoCopyright={#TheCopyright} {#TheCompany}
VersionInfoDescription={#TheAppName}
VersionInfoProductName={#TheAppName}
VersionInfoProductVersion={#TheAppVersion}
VersionInfoTextVersion={#TheAppVersion}
VersionInfoVersion={#TheAppVersion}.{#TheSetupVersionBuild}
;
AlwaysShowDirOnReadyPage=true
AlwaysShowGroupOnReadyPage=true
AlwaysUsePersonalGroup=false
DefaultDirName={pf}\{#TheAppVeryShortName}
DefaultGroupName={#TheAppName}
DisableDirPage=false
DisableProgramGroupPage=false
PrivilegesRequired=admin
UsePreviousAppDir=true
UsePreviousGroup=true
UserInfoPage=true
UpdateUninstallLogAppName=false
;
LicenseFile=License.rtf
InfoBeforeFile=Intro.rtf
InfoAfterFile=Extro.rtf
;
Compression=lzma
InternalCompressLevel=max
SolidCompression=true
;
FlatComponentsList=false
SetupIconFile={#TheAppVeryShortName}.ico
ShowLanguageDialog=auto
Uninstallable=true
UninstallDisplayIcon={uninstallexe}
WizardImageFile=DGAIinstaller.bmp
; compiler:wizmodernimage-is.bmp
WizardSmallImageFile=DGAIinstallersmall.bmp
; compiler:wizmodernsmallimage-is.bmp
WizardImageStretch=false
;
SourceDir=D:\Development\DGXLAI\Setup
OutputDir=D:\Development\DGXLAI\Setup\Installer
OutputBaseFilename={#TheSetupRoot}_{#TheSetupVersion}_{#TheSetupVersionBuild}_Setup

I have added the following instructions for making sure the setup has access to 32-bit registry sections on 32-bit systems and 64-bit and 32-bit registry sections on 64-bit systems

; Specifically permit running 64bit mode, since we need this to read from the
; correct node in the Registry.
; "ArchitecturesAllowed=x64" specifies that Setup cannot run on anything but x64.
; "ArchitecturesInstallIn64BitMode=x64" requests that the install be done
; in "64-bit mode" on x64, meaning it should use the native 64-bit Program Files
; directory and the 64-bit view of the registry.
;
ArchitecturesAllowed=x86 x64
ArchitecturesInstallIn64BitMode=x64

The next items deal with the UI (I’m a Tahoma fan) and the languages. see InnoSetup’s help for additional info.

[LangOptions]
DialogFontName=Tahoma
TitleFontName=Tahoma
WelcomeFontName=Tahoma
CopyrightFontName=Tahoma
[Languages]
Name: english; MessagesFile: compiler:Default.isl
Tasks, Types and Components section

The Tasks, Types and Components section. I’ve found this to be particular difficult to understand and the on-line help of Inno does not really assist you, each section is basically described but what I really miss is the explanation between the various parameters and the sections where you can use these. For instance, entries under Components link to entries under Types but also under Tasks and then also under Files and Icons, and … enfin, probably I’ve not even covered all the possibilities. Nevertheless, after some genuine trial-and-error I’m relatively satified with the result.

[Tasks]
;
; Assign "Tasks: desktopicon" as a parameter to those elements that need to be processed when this task is selected.
;
Name: desktopiconhtml; Description: {cm:CreateDesktopIcon} for HTML Help; GroupDescription: {cm:AdditionalIcons}; Components: full\htmlhelp
Name: desktopiconpdf; Description: {cm:CreateDesktopIcon} for PDF Help; GroupDescription: {cm:AdditionalIcons}; Components: full\htmlhelp

[Types]
Name: full; Description: Complete installation
Name: addin; Description: {#TheAppName} Add-In only
Name: custom; Description: Custom installation; Flags: iscustom

[Components]
Name: full; Description: {#TheAppName}; Types: full addin
Name: full\addin; Description: {#TheAppShortName} Add-In for Microsoft Excel; Types: full addin
#if WildcardExists('..\Help\Output\' + TheAppRoot + '.CHM') 							/* Conditional compilation */
Name: full\htmlhelp; Description: {#TheAppShortName} HTML and PDF Help; Types: full
#endif
#if WildcardExists('Lexicons\*.LEX') 													/* Conditional compilation */
Name: full\lexicon; Description: {#TheAppShortName} Language Lexicons; Types: full
#endif
#if WildcardExists('Sample Projects\*.XL*') 											/* Conditional compilation */
Name: full\samples; Description: Sample Projects; Types: full
#endif
Files section

The Files section is where all the files from my system are embedded in the setup package. The script copies the UnInstall.ico only for creating an iconised “uninstall application” Start Menu entry. Such menu entry, still pretty common, is nevertheless deprecated by Microsoft that recommends creating uninstaller entries exclusively under the Control Panel’s Programs and Features (Vista or 7) or Add or Remove Programs (with XP) and in the future I will follow this recommendation.

[Files]
Source: UnInstall.ico; DestDir: {app}; Flags: ignoreversion replacesameversion overwritereadonly; Components: full
;
; NOTE: Don't use "Flags: ignoreversion" on any shared system files
;
; Microsoft Excel Add-Ins need to be installed in a specific folder so that the Add-Ins Manager (uses a registry entry called
; "HKCU\Software\Microsoft\Office\<version>\Excel\Options") will treat them natively via a link stored as "OPENx" key ("x" can
; be blank or "1", "2", ...) instead of under the registry key "HKCU\Software\Microsoft\Office\<version>\Excel\Add-in Manager".
; This folder is usually '{userappdata}\Microsoft\Add-Ins' but Excel may have another storage locations.
;
; These components have 'dontcopy' flag, have no destination and will be copied to the final folder via [Code].
; Source: ..\{#TheAppRoot}.XLA; DestDir: {app}; Flags: ignoreversion replacesameversion overwritereadonly; Components: full\addin
;
Source: ..\{#TheAppRoot}.XLA; Flags: dontcopy touch; Components: full\addin
Source: ..\{#TheAppRoot}.XLAM; Flags: dontcopy skipifsourcedoesntexist touch; Components: full\addin
;
Source: ..\Documentation\{#TheAppShortName} Release, Installation and Quick Reference.pdf; DestDir: {app}; Flags: overwritereadonly ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\addin
Source: ..\Documentation\readme.txt; DestDir: {app}; Flags: isreadme overwritereadonly skipifsourcedoesntexist ignoreversion replacesameversion touch; Components: full\addin
Source: ..\Documentation\WhatsNew.txt; DestDir: {app}; Flags: overwritereadonly skipifsourcedoesntexist ignoreversion replacesameversion touch; Components: full\addin
;
#if WildcardExists('..\Help\Output\' + TheAppRoot + '.CHM')								/* Conditional compilation */
Source: ..\Help\Output\{#TheAppRoot}.CHM; DestDir: {app}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\addin
Source: ..\Help\Output\{#TheAppRoot} (v{#TheAppVersion}).PDF; DestDir: {code:GetHTMLHelpPath}; DestName: {#TheAppRoot}.PDF; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\htmlhelp
Source: ..\Help\Output\{#TheAppRoot}.HTM; DestDir: {code:GetHTMLHelpPath}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\htmlhelp
Source: ..\Help\Output\files\*; DestDir: {code:GetHTMLHelpPath}files; Flags: ignoreversion recursesubdirs createallsubdirs skipifsourcedoesntexist replacesameversion sortfilesbyextension touch; Components: full\htmlhelp
#endif
#if WildcardExists('Lexicons\*.LEX') 													/* Conditional compilation */
Source: Lexicons\*.LEX; DestDir: {code:GetLexiconPath}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion overwritereadonly sortfilesbyextension touch; Components: full\lexicon
#endif
#if WildcardExists('Sample Projects\*.XL*') 											/* Conditional compilation */
Source: Sample Projects\*.XL*; DestDir: {code:GetSamplesPath}; Flags: ignoreversion recursesubdirs createallsubdirs skipifsourcedoesntexist replacesameversion overwritereadonly sortfilesbyextension touch; Components: full\samples
#endif

My Add-In uses components from the VB6 Runtime Modules. These modules are normally not installed but are vital to the correct functioning of the Add-In. Hence, I’ve included a small script obtained from Inno’s knowledge base, that includes these files for me. I’ve also added a few components that I have found “missing” on Windows 7 but are being used by my Add-In.

#include "D:\Development\Common\SetupScripts\VBRunTimes.iss"

Click below to expand this script:

[Files]
; ===========================================================
; Begin VB system files (from VBRun60SP6.EXE) - 32bit
;
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\asycfilt.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\msvbvm60.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\oleaut32.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\olepro32.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regserver noregerror; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\comcat.dll";   DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regserver noregerror; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\stdole2.tlb";  DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regtypelib noregerror; Components: full
;
; End VB system files
; ===========================================================
; Specific controls included in the project but found missing on Windows 7 - 32bit.
; Always use the following parameters for an OCX:
; ...; DestDir: "{sys}"; Flags: restartreplace sharedfile regserver
;
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\comdlg32.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\mscomct2.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\mscomctl.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
; ===========================================================
Icons section

The Icons section. The UnInstall entry, mentioned above, is also present (as is the reference to the icon file) .

Some entries use a User Defined Function retrieving the name of a custom folder on the destination PC where I commonly install samples, help files, and more.

[Icons]
;
; Start menu icons.
;
Name: {group}\Release Notes, Installation Guide and Quick Reference; Filename: {app}\{#TheAppShortName} Release, Installation and Quick Reference.pdf; WorkingDir: {app}; Flags: createonlyiffileexists; Components: full\addin
Name: {group}\Read Me; Filename: {app}\readme.txt; WorkingDir: {app}; Components: full\addin
Name: {group}\What Is New; Filename: {app}\WhatsNew.txt; WorkingDir: {app}; Components: full\addin
Name: {group}\{cm:UninstallProgram,{#TheAppName}}; Filename: {uninstallexe}; IconFilename: {app}\UnInstall.ico; IconIndex: 0
Name: {group}\{#TheAppName} Documentation (PDF); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.PDF; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp
Name: {group}\{#TheAppName} Documentation (HTML); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.HTM; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp
;
; Desktop icons. See "[Tasks]" for further details
;
Name: {code:GetDesktop}\{#TheAppName} Documentation (PDF); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.PDF; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp; Tasks: desktopiconpdf
Name: {code:GetDesktop}\{#TheAppName} Documentation (HTML); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.HTM; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp; Tasks: desktopiconhtml
Run Section

In the Run section I allow Excel to start at the end of the installation process. It uses shellexec so that it does not need an exact path to excel.exe.

[Run]
; Without 'shellexec' this command requires exact path to the excel.exe file which is soooo version dependent
Filename: Excel.exe; Description: {cm:LaunchProgram,Microsoft Excel}; Flags: shellexec nowait postinstall skipifsilent unchecked
Registry section

In the Registry section I prepare the registry for my application.

[Registry]
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}; Flags: uninsdeletekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\QueryLibrary; Flags: uninsdeletekey dontcreatekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\ToolBar; Flags: deletekey uninsdeletekey dontcreatekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\MRUD; Flags: uninsdeletekey

I also have a specific entry that enables me to use the Common Dialog Control on systems where this component is installed but fails to initialise in Late Binding (which is what I use). Without this entry, you cannot use the MSComDlg.CommonDialog class in your code. This class is embedded in COMDLG32.OCX which is a creatable control only on systems where a development environment (and as such a proper license) is available. To allow creating this class on non-development PCs, you can add this license with this particular key and value (Default):

Root: HKCR; Subkey: Licenses\4D553650-6ABE-11cf-8ADB-00AA00C00905; ValueType: string; ValueData: gfjmrfkfifkmkfffrlmmgmhmnlulkmfmqkqj; Flags: createvalueifdoesntexist
Excel specific script

The next statement includes the script containing the complete logic for the installation, activation, deactivation, update and removal of the Add-In. This script also uses the definitions given at the beginning of this page. It will be described on the next page.

#include "D:\Development\Common\SetupScripts\ExcelAddinScript.iss"

Finally, this last statement closes the script, which I use for debugging purposes:

#expr SaveToFile("debug.iss")

Previous Page | Next Page

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

Next Page »

Create a free website or blog at WordPress.com.