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

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:

• InitializeSetup
• InitializeWizard
• CurStepChanged
• NextButtonClick
• ShouldSkipPage
• UpdateReadyMemo
• RegisterPreviousData
• InitializeUninstall
• InitializeUninstallProgressForm
• CurUninstallStepChanged

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

• GetSamplesPath
• GetHTMLHelpPath
• GetLexiconPath
• CreateExcelApp
• KillExcelApp
• HookAddinToExcel
• UnhookAddinFromExcel
• CopyTheAddIn
• RemoveTheAddIn
• DoPreInstall
• DoPostInstall
• DoSuccessInstall
• DoPreUnInstall
• DoPostUnInstall

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