Dutch Gemini's Weblog Pages

September 5, 2011

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

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

Building up the Script

An installer for an Excel Add-In consist in an application-specific part and an “Excel” part. During the application-specific part all application-related files are managed, like copying files, samples, user manuals and references, etc. During the “Excel” part all actions are carried out for hooking the Add-In into Excel.

Following this approach, I have designed the install script in such way that the application-related part is the main script and the Excel part is taken care of by an #include script with the only [Code] section that performs the factual installation (and removal) of the Add-In. The included script uses a set of #define statements and compiler directives inherited from the main script. I will focus on the included script and wrap it all up at the end of my story.

From InnoSetup I have used the following event routines for my customisation:

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

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 is also used to detect compatibility with the third-party application. Failure to comply will abort the installation.

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.

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

NextButtonClick – Marks the selected components and will trap whether Excel is running before it is ready to install the files (from the [Files] section). Also used to memorise the values of the custom dialogs (see above).

ShouldSkipPage – Detects whether or not to show certain custom dialogs.

UpdateReadyMemo – Prepares the report of what the installer is about to do, including settings for custom dialogs.

RegisterPreviousData – Stores the user’s selection of the custom dialogs.

InitializeUninstall – Traps whether Excel is running or not.

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. Note: this event procedure is not properly documented.

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

Support functions

The following user-defined functions were created in support of the actions:

Code snippets

CheckVBE6DLLVersion

function CheckVBE6DLLVersion(): Boolean;
(*
	Checks the version of VBE6.DLL.
	Excel can crash adding code programmatically when the version of VBE6.DLL is equal to or
	older than 6.5.10.24. This version of the DLL can be found all the way up to Excel 2007 and
	it not automatically upgraded by Microsoft in its Office services packs or updates. Apparently
	starting Excel 2007 SP2 a more recent and "bug-free" VBE6.DLL is being distributed.
*)
var
	VBE6DLLPath: String;
	VersionNumberString: String;
	VerMajorMinor: Cardinal;
	VerBuild: Cardinal;
	VerMajorMinorNotGood: Cardinal;
	VerBuildNotGood: Cardinal;
begin
	// Default.
	//
	Result := true;

	// The rule for computing the values are:
	//
	//		Major & Minor: ( shl 16) or
	//		Revision & Build: ( shl 16) or
	//
	VerMajorMinorNotGood := (6 shl 16) or 5;
	VerBuildNotGood := (10 shl 16) or 24;

	// Fetch the location of VBE6.DLL, the version numbers of this file and compare.
	//
	RegQueryStringValue(HKEY_LOCAL_MACHINE, 'SOFTWARE\Microsoft\VBA','Vbe6DllPath', VBE6DLLPath);

	if GetVersionNumbers(VBE6DLLPath, VerMajorMinor, VerBuild) then begin
		// Get a string with the data.
		//
		GetVersionNumbersString(VBE6DLLPath, VersionNumberString);

		// Compare.
		//
		if not ((VerMajorMinor > VerMajorMinorNotGood) or ((VerMajorMinor = VerMajorMinorNotGood) and (VerBuild > VerBuildNotGood))) then begin
			// Oops outdated version of VBE6.DLL'
			//
			if IsRunningSilent then begin
				Log('Incompatible version of VBE6.DLL found. See ''README.TXT'' for details. Contact a System Administrator.');
			end else begin
				MsgBox('Warning: incompatible version of VBE6.DLL found.' + #13#10 + #13#10 +
					'An incompatible version of a Visual Basic Environment module (VBE6.DLL) is installed on this computer (' + VersionNumberString + '). ' + GetMsgBoxCrLfCharacter +
					'This module can under certain circumstances cause Excel to crash when used in junction with {#TheAppName}. ' + GetMsgBoxCrLfCharacter +
					'It is highly recommended that you upgrade this module to a newer version.' + #13#10 + #13#10 +
					'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
			end;
			Result := false;
	    end;
	end else begin
		// This is Ok for the moment.
		//
		//	MsgBox('Warning: VBE6.DLL not found.' + #13#10 + #13#10 +
		//		'An module of the Visual Basic Environment is missing or could not be retrieved.' + #13#10 + #13#10 +
		//		'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
		//	Result := (true Or false);
	end;
end;

GetDesktop

function GetDesktop(S: String): String;
(*
	Return the path to the Desktop.
*)
begin
	if (InstallMode = 0) then begin
		Result := GetExpandedConstant('{userdesktop}', '{commondesktop}', '');
	end else begin
		Result := GetExpandedConstant('{commondesktop}', '', '');
	end;
end;

GetExcelVersionNumberAsNumber

function GetExcelVersionNumberAsNumber(): Integer;
(*
	Search Registry for installed Excel version. From highests version down. This extra
	function was created because 'StrToIntDef()' cannot handle transformation of strings
	containing 'decimals', which is what GetExcelVersionNumberAsString() returns.
*)
begin
	if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\14.0\Excel\InstallRoot', 'Path') then begin
		Result := 14;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\12.0\Excel\InstallRoot', 'Path') then begin
		Result := 12;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\11.0\Excel\InstallRoot', 'Path') then begin
		Result := 11;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\10.0\Excel\InstallRoot', 'Path') then begin
		Result := 10;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\9.0\Excel\InstallRoot', 'Path') then begin
		Result := 9;
	end else begin
		Result := 0;
	end;
end;

GetExcelVersionNumberAsString

function GetExcelVersionNumberAsString(): String;
(*
	Search Registry for installed Excel version. From highests version down.
*)
begin
	if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\14.0\Excel\InstallRoot', 'Path') then begin
		Result := '14.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\12.0\Excel\InstallRoot', 'Path') then begin
		Result := '12.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\11.0\Excel\InstallRoot', 'Path') then begin
		Result := '11.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\10.0\Excel\InstallRoot', 'Path') then begin
		Result := '10.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\9.0\Excel\InstallRoot', 'Path') then begin
		Result := '9.0';
	end else begin
		Result := '0.0';
	end;
end;

GetExpandedConstant

function GetExpandedConstant(const First, Second, Default: String): String;
(*
	Sometimes IS cannot expand constants and throws up exceptions.
	This happens for instance with {userdocs} when it is mapped to a network drive.
*)
var
	ResultString: String;
	ExceptionRaised: Boolean;
begin
	// Try first constant.
	//
	ExceptionRaised := false;
	try
		ResultString := ExpandConstant(First);
	except
		ExceptionRaised := true;
	end;

	// Try second is specified.
	//
	if ExceptionRaised then begin
		if (Length(Trim(Second)) > 1) then begin
			ExceptionRaised := false;
			try
				ResultString := ExpandConstant(Second);
			except
				ExceptionRaised := true;
			end;
		end;
	end;

	// Return whatever default.
	//
	if ExceptionRaised then begin
		ResultString := Default;
	end;

	Result := ResultString;
end;

GetMsgBoxCrLfCharacter

function GetMsgBoxCrLfCharacter(): String;
(*

	Vista and W7 perform a UI-aware formatting of the text in a MsgBox breaking up lines at unpredictable positions
	so we can not insert hard carriage return/linefeeds in the text to break lines where we want. In earlier versions
	of Windows this is OK. Therefore, on Vista or W7 we return a space and let it handle formatting and use a hard CrLf
	in all other cases.

*)
var
	WindowsVersion: Integer;
begin
	WindowsVersion := (GetWindowsVersion shr 24);
	if (WindowsVersion >= 6) then begin
		Result := ' ';
	end else begin
		Result := #13#10;
	end;
end;

IsExcel2007OrHigher

function IsExcel2007OrHigher(): Boolean;
(*
	Excel 2007 has version number 12, so if the version on board is 12 or higher then
	it is Excel 2007 or higher. With Excel 2007+, XLAM Add-Ins formats can be used.
*)
begin
	Result := false;

	if (GetExcelVersionNumberAsNumber >= 12) then Result := true;
end;

IsExcel64Bit

(*
	Importing a Windows API function for understanding if EXCEL.EXE is a 32-bit
	or 64-bit application. This function has a few named constants. There appears
	to be no other accurate option than looking at the exe's PE header.
*)
const
	SCS_32BIT_BINARY = 0;	// A 32-bit Windows-based application
	SCS_64BIT_BINARY = 6;	// A 64-bit Windows-based application
	SCS_DOS_BINARY   = 1;	// An MS-DOS – based application
	SCS_OS216_BINARY = 5;	// A 16-bit OS/2-based application
	SCS_PIF_BINARY   = 3;	// A PIF file that executes an MS-DOS – based application
	SCS_POSIX_BINARY = 4;	// A POSIX – based application
	SCS_WOW_BINARY   = 2;	// A 16-bit Windows-based application

function GetBinaryType(lpApplicationName: AnsiString; var lpBinaryType: Integer): Boolean;
	external 'GetBinaryTypeA@kernel32.dll stdcall';

function IsExcel64Bit(): Boolean;
(*
	64-bit versions of Office use the standard node of the Registry named
	'Software\Microsoft\' which is the same for 32-bit Excel on 32-bit Windows.
	The node named 'Software\Wow6432Node\Microsoft\' is only used for 32-bit
	Excel installed on 64-bit Windows so we can avoid checking.

	Makes sure you have these [Setup] directives correctly set otherwise it may
	not read from the correct branch in the Registry on 64-bit systems:
		ArchitecturesAllowed=x86 x64
		ArchitecturesInstallIn64BitMode=x64
*)
var
	InstallRoot: String;
	BinaryType: Integer;
begin
	Result := false;

	// Look for the InstallRoot of Excel. This is where Excel is installed.
	//
	if not RegQueryStringValue(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\InstallRoot', 'Path', InstallRoot) then exit;

	// Look what binary type 'EXCEL.EXE' is.
	//
	if GetBinaryType(AddBackslash(InstallRoot) + 'excel.exe', BinaryType) then begin
		Result := (BinaryType = SCS_64BIT_BINARY);
	end;
end;

IsExcelRunning

function IsExcelRunning(const Typ: TMsgBoxType; const Buttons, Default: Integer; var MsgBoxResult: Integer): Boolean;
(*
	Checks whether Excel is running in memory, by hooking to the COM Automation Object
	'Excel.Application'. If the object exists, then Excel is running.

	Another way would be scanning WMI processes for 'EXCEL.EXE' (simplified, no exception handling, no message boxes, etc.):

	function IsExcelRunning(): Boolean;
	var
		loLocator: Variant;
		loWMI: Variant;
		loProcesses: Variant;
	begin
		Result := false;
		loLocator := CreateOleObject('WBEMScripting.SWBEMLocator');
		loWMI := loLocator.ConnectServer();
		loWMI.Security_.ImpersonationLevel := 3; // Impersonate
		loProcesses := loWMI.ExecQuery('Select * from Win32_Process Where Name = ''excel.exe''');

		if (loProcesses.Count > 0) then Result := true;

		loProcesses := Unassigned;
		loWMI := Unassigned;
		loLocator := Unassigned;
	end;
*)
var
	ExcelRunning: Variant;
begin
	// Try to get an active Excel COM Automation object. If True, then Excel is Running.
	// The installation cannot continue if Excel is running, so we start with 'not running'.
	//
	Result := false;

	// Try to get an active Excel COM Automation object.
	//
	try
		ExcelRunning := GetActiveOleObject('Excel.Application');
	except
		if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('Microsoft Excel not running (this is Ok).');
	end;

	if Not VarIsEmpty(ExcelRunning) then begin
		Result := true;
		if IsRunningSilent then begin
			Log('Microsoft Excel is running, cannot continue. Contact a System Administrator.');
			MsgBoxResult := Default;
		end else begin
			MsgBoxResult := MsgBox('Oops! Microsoft Excel is running.' + #13#10 + #13#10 +
				'The {#TheAppName} can only be installed when Microsoft Excel' + GetMsgBoxCrLfCharacter +
				'is not running. Close all active sessions of Microsoft Excel and retry.' + #13#10 + #13#10 +
				'If this notification is unexpected, contact a System Administrator.', Typ, Buttons);
		end;
	end;

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

IsInstaller

function IsInstaller(): Boolean;
(*
	IsInstaller() in itself does not exist but since it is the opposite
	of IsUninstaller, we can use the latter to mimic the missing function.
*)
begin
	Result := not IsUninstaller;
end;

IsRunningSilent

function IsRunningSilent(): Boolean;
(*
	Indicates if the Installer or UnInstaller is running "silent".
*)
begin
	if IsInstaller then begin
		try
			Result := WizardSilent;
		except
			Result := false;
		end;
	end else begin
		try
			Result := UninstallSilent;
		except
			Result := false;
		end;
	end;
end;

IsTrustAddInsAndTemplatesChecked

function IsTrustAddInsAndTemplatesChecked(): Boolean;
(*
	Check if 'Trust all installed add-ins and templates' setting is turned 'On'.
	This is a particular Registry setting and impacts on Excel's capability to
	execute macro code in Add-Ins.
*)
var
	DontTrustInstalledFiles: Cardinal;
begin
	// This value is established by Excel when not available in the Registry so we need to start 'True'.
	//
	Result := true;

	// Fetch the setting of 'Trust all installed add-ins and templates'. If '0' then it is enabled.
	//
	if RegQueryDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
											 '\Excel\Security','DontTrustInstalledFiles', DontTrustInstalledFiles) then begin
		Result := (DontTrustInstalledFiles = 0);
	end;
end;

IsTrustVBProjectChecked

function IsTrustVBProjectChecked(): Boolean;
(*
	Check if 'Trust access to Visual Basic project' setting is turned 'On'.
	This is a particular Registry setting and impacts on Excel's capability to
	execute macro code in Add-Ins.
*)
var
	AccessVBOM: Cardinal;
begin
	// This value is established by Excel when not available in the Registry so we need to start 'True'.
	//
	Result := true;

	// Fetch the setting of 'Trust access to Visual Basic project'. If '1' then it is enabled.
	// Note: Excel 2000 does not have this setting so we keep the default 'true'.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		if RegQueryDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
												 '\Excel\Security','AccessVBOM', AccessVBOM) then begin
			Result := (AccessVBOM = 1);
		end;
	end;
end;

SetTrustAddInsAndTemplates

function SetTrustAddInsAndTemplates(): Boolean;
(*
	Turns the 'Trust all installed add-ins and templates' setting 'On'.
	This is a particular Registry setting and impacts on Excel's capability
	to execute macro code in Add-Ins.
*)
begin
	Result := false;

	// Set the setting of 'Trust all installed add-ins and templates'. If '0' then it is enabled.
	//
	RegWriteDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
										  '\Excel\Security','DontTrustInstalledFiles', 0)

	// Return the result.
	//
	Result := IsTrustAddInsAndTemplatesChecked;
end;

SetTrustVBProject

function SetTrustVBProject(): Boolean;
(*
	Turns the 'Trust access to Visual Basic project' setting 'On'.
	This is a particular Registry setting and impacts on Excel's capability
	to execute macro code in Add-Ins.
*)
begin
	Result := false;

	// Set the setting of 'Trust access to Visual Basic project'. If '1' then it is enabled.
	// Note: Excel 2000 does not have this setting but it probably ignores it.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		RegWriteDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
											  '\Excel\Security','AccessVBOM', 1)

		// Return the result.
		//
		Result := IsTrustVBProjectChecked;
	end else begin
		Result := true;
	end;
end;

Previous Page | Next Page

Advertisements

13 Comments »

  1. First of all, thank you for posting parts of your script.

    In your code that checks if Excel is 64-bit, why not just hardcode ‘14.0’ into the registry key name, since (as far I know) only Office 2010 can be installed in 64-bit version?

    Comment by JP — September 8, 2011 @ 5:04 pm | Reply

    • ‘14.0’ is insufficient to test for 32- or 64-bit Excel, since Excel 2010 can be installed in either version.

      Comment by Jon Peltier — December 29, 2011 @ 7:54 pm | Reply

      • That’s why I’ve added a function checking for 64bit.

        Comment by dutchgemini — January 2, 2012 @ 10:10 am

      • Dutch – That’s what I meant by my comment to JP. Office 2010 introduced 64-bit versions, but also includes a 32-bit option.

        Comment by Jon Peltier — January 2, 2012 @ 5:34 pm

  2. In principle you are right, but I have the tendency to think ahead and parametrise as much as possible every function. I also expect Microsoft to develop other versions of Office/Excel beyond 2010 that support 64-bit so I do not need to touch this function anymore.

    Dutch

    Comment by dutchgemini — September 9, 2011 @ 8:23 am | Reply

  3. You’re right that future versions of Excel will probably support 64-bit, but I don’t see how you wouldn’t need to edit the function. You’ll still need to add each new Excel version to GetExcelVersionNumberAsString. What I would do is define an array of version numbers as a global at the top of the script, and loop through it wherever necessary.

    Comment by JP — September 9, 2011 @ 12:00 pm | Reply

  4. I did consider that while thinking about when the next version of Excel will come out. The reason why it is currently hardcoded is that the code written this way is simple, hits the target very efficiently and is [for me] understandable. I evaluated that using loops on arrays or with counters would have added an excess degree of complexity and likely have the same amount of code lines of my solution, apart from requiring that the structure of Excel-related registry keys remain basically as they are now.

    Comment by dutchgemini — September 9, 2011 @ 1:26 pm | Reply

  5. I have found out that the IsExcel64Bit() function does not work correctly and will correct this asap.

    Comment by dutchgemini — June 13, 2012 @ 4:15 pm | Reply

    • I have corrected the function and now rely on Windows telling me whether the EXCEL.EXE is a 32-bit executable or a 64-bit executable, as this seems to be the only reliable source.

      Comment by dutchgemini — June 14, 2012 @ 2:11 pm | Reply

  6. I have Windows 7 64-Bit and Office 2010 64-bit installed. GetExcelVersionNumberAsString is returning 0.0. I can see that in the Registry Path exists under:
    ‘HKLM\Software\Microsoft\Office\14.0\Excel\InstallRoot’

    Here is the saved registry path:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Excel\InstallRoot]
    “Path”=”C:\\Program Files\\Microsoft Office\\Office14\\”

    What is going wrong?

    Joginder NAhil

    Comment by Joginder Nahil — June 19, 2012 @ 4:08 pm | Reply

    • Nothing is going wrong.

      To “see” the registry node you mention, which is the 64-bit node for 64-bit applications, you need to enable InnoSetup to behave as a 64-bit application on a 64-bit OS. If you don’t, and this is the default with InnoSetup, it behaves like a 32-bit application and looks only in the node for 32-bit applications, which on 64-bit Windows happens to be [HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Office\14.0\Excel\InstallRoot]

      Add this to your [Setup] section:

      ;
      ; 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

      Comment by dutchgemini — June 19, 2012 @ 4:18 pm | Reply

  7. Just want to say… Thanks!!!!

    Comment by Luis — November 23, 2012 @ 3:30 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: