Installing .xla add-in via the registry

[RB Smissaert]

Could somebody tell me again what the most foolproof way is to install a
..xla add-in in Excel (ticked under Tools, Add-ins) via the registry.

If there really isn't a foolproof way I think it might be better to do it in
Excel itself via a little VB6 .exe file.


RBS

 

[Bob Phillips]

Bart,

It was all in that installer file I sent you a while back.

 

[RB Smissaert]

Bob,

Yes, I thought of that, but wasn't that done via loading Excel (in memory)
via a VB6 .exe?
I want to try it via the registry via an INNO install file.
Was there a source file with it?
Will have a look at your file.

Bart

 

[Bob Phillips]

I'll try and dig out the file and post the source to you.

Regards

Bob

 

[RB Smissaert]

Thanks Bob.


I have persisted with a registry install for a long time, but finally
decided that it just wasn't reliable enough.
This was my registry routine. It is in an INNO script, but you will
understand.


procedure setExcelVariables;
var
strExcelVersion : String;
iDotPos1 : Integer;
i : Integer;

// for getting the Office version and the path to Excel like this:
// C:\Program Files\Microsoft Office\Office10\
// note that the Office version can be a non-integer number like 9.5
// -----------------------------------------------------------------

begin

RegQueryStringValue(HKEY_LOCAL_MACHINE,
'SOFTWARE\Microsoft\Windows\CurrentVersion\App
Paths\Excel.exe',
'Path',
strPathToExcel);

// this will be file version of Excel.exe, something like 10.0.2.26
GetVersionNumbersString(strPathToExcel + 'EXCEL.EXE', strExcelVersion)

// Extract the registry version, such as 10.0
// ------------------------------------------------

// get the first dot
iDotPos1 := Pos('.', strExcelVersion);
i := iDotPos1 + 1;

// to find the second dot
While (strExcelVersion <> '.') and (i <= Length(strExcelVersion)) do
begin
i := i + 1
end;

// this will then be something like 10.0
strOfficeVersion := Copy(strExcelVersion, 1, i - 1);

end;



// Return the Subkey where the add-in should be added
// --------------------------------------------------
function GetAddInSubKey(Param: String): String;
begin
Result := 'Software\Microsoft\Office\' + strOfficeVersion + '\Excel\Add-in
Manager';
end;



// Return the Options Subkey
// -------------------------
function GetOptionsSubKey(Param: String): String;
begin
Result := 'Software\Microsoft\Office\' + strOfficeVersion +
'\Excel\Options';
end;



// Get the first available OPEN entry name
// ------------------------------------------------
function GetOPEN(Param : String): String;
var
i: Integer;
strValue : String;
strSubKey : String;
begin
strSubKey := 'Software\Microsoft\Office\' + strOfficeVersion +
'\Excel\Options';

if not RegQueryStringValue(HKEY_CURRENT_USER,
strSubKey,
'OPEN',
strValue) then
Result := '';
i := 1;

while RegQueryStringValue(HKEY_CURRENT_USER,
strSubKey,
'OPEN'+ IntToStr(i),
strValue) and
(Pos('synergyreportingloader.xla', Lowercase(strValue)) = 0) do
begin
i := i + 1;
End;

Result := 'OPEN' + IntToStr(i);
end;


This was the best I could come up with, but still I wasn't convinced it
would be foolproof.

Now do it simpley like this with a VB6 exe, a bit slower, but reliable:

Sub Main()

Dim oXL As Object
Dim oAddin As Object
Dim strLocalDrive As String

Set oXL = CreateObject("Excel.Application")

strLocalDrive = Left$(oXL.Path, 1)

oXL.Workbooks.Add
Set oAddin = _
oXL.AddIns.Add(strLocalDrive & _
":\RBSSynergyReporting\Program\SynergyReportingLoader.xla",
True)
oAddin.Installed = True

oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing

End Sub


Looking at your installer .ini file you use the registry and I will be
insterested to see how you did it.


RBS