Registering an XLL permanently
andste
When running an XLL, the following VB.NET code registers an additional
XLL add-in at runtime...
...
Dim RegID As Object = Nothing
Dim Result As XlCall.XlReturn = XlCall.TryExcel(XlCall.xlfRegister,
RegID, "C:\MyDirectory\MyAddIn.xll")
...
Unfortunatly, MyAddIn.xll is not available when Excel is restarted.
How can I register an XLL at runtime "permanently", in the sense that
it is available the next time Excel is started, such that the XLL
shows up and is ticked in the dialog ALT-t-i?
Thank you in advance,
Andi
Govert van Drimmelen
Excel stores the list of enabled add-ins in the Windows registry
(key:
HKCU/Software/Microsoft/Office/$version/Excel/Options, values: OPEN,
OPEN1, OPEN2 etc.). The installer should just add another OPENx key.
If you have a look at how that registry key changes when you make
changes in the Alt-t,i dialog, you'll figure out the format and extra
parameters - I think you need a /r or something there too.
From your VB.NET you then just write to the registry, taking some care
with the Excel version and enumerating the OPENx keys to figure out
what the next one is you need to add.
Regards,
Govert
andste
having to mess around with PIAs.
So what I would be interested in is a C API solution for permanently
registerung XLLs.
Rgrds,
Andi
Govert van Drimmelen
To my knowledge, the Excel C API does not support such add-in
installation.
Writing to the registry would be the cleanest approach - you'd
basically be doing exactly what Excel does when you go to the add-ins
dialog box, and what an installer would do as part of the .xll
installation. It has not changed for many Excel versions, so should
work uniformly across the wide range of Excel versions you support.
Regards,
Govert
andste
What I might do is implement two installation procedures: 1) Auto
Install: a proper exe/msi installer that messes around in the windows
registry, requiring admin rights 2) Manual Install: does not require
admin rights, setup by following about 6 instructions (something which
90% of all users are not able to do...).
Do you happen to know any good resources explaining XLL registry entry
management for Excel 95, 97, 2000, 2003, XP, 2007 & 2010? (If I do it,
I do it properly).
Rgrds,
Andi
Govert van Drimmelen
xlAutoAdd (http://msdn.microsoft.com/en-us/library/bb687898.aspx) is
called by Excel on the .xll when the add-in is added to the add-ins
dialog. So that won't help you.
---------
The user does not need administrator rights for you to install an add-
in. The change you need to make is in the user's part of the registry
under HKEY_CURRENT_USER. Excel often writes entries here as it runs,
as does Excel-DNA when loading ribbons or RTD servers.
The xll registry entry you need to make for an .xll is the OPENx key I
mentioned earlier under (key:
HKCU/Software/Microsoft/Office/$version/Excel/Options, values: OPEN,
OPEN1, OPEN2 etc.). The installer should just add another OPENx key.
The value data would be "\R c:\...path to...\TheAddin.xll". Have a
look using RegEdit and you should easily see what is going on.
Google has good hits for "VB.NET registry" that should get you started
manipulating the registry. The interesting classes are
Microsoft.Win32.Registry (http://msdn.microsoft.com/en-us/library/
microsoft.win32.registry(v=VS.100).aspx) and possibly
Microsoft.Win32.RegistryKey.
So it should not be hard to make an add-in 'self-installing' in the
sense that you can run the add-in by double-clicking, and it will
(perhaps after a prompt) set the registry keys that add it as an
active add-in that is opened when Excel starts. You could also make a
'master' add-in that installs and loads other add-ins, allowing some
kind of auto updating.
All of this would work without requiring admin privileges.
------
The biggest advantage to making a proper installer would be to have
the installer figure out whether to install the 32-bit or 64-bit
version of the add-in, based on the version of Office 2010 that is
installed. Learning about making a proper .msi installer has a pretty
steep learning curve though. The Wix toolkit is probably the first one
to look at: http://wix.sourceforge.net/. I hope to give it a go myself
some day. There is some stuff I could build into Excel-DNA to make
that scenario easier, but for now you'd need to add a bit of VBScript
or something to the installer to do the OPENx key enumeration.
(Implementing registration of an .xll add-in for all users - which
would require administrator privileges - is quite difficult and was
discussed in an earlier thread from this week
http://groups.google.com/group/exceldna/t/87fe183d2c670bbe)
Regards,
Govert
andste
I also did some digging in this forum here. Deployment functionality
seems
to be a very relevant topic. It might not be a top priority, but
integrating some basic deployment helpers within Excel-DNA might be a
differenting factor,
I will report back what I implement.
Rgrds,
Andi
Govert van Drimmelen
Thanks for writing back - great job on sorting out those old Excel
versions!
What happens if you don't register in the Add-In Manager part? I was
under the understanding that you only need the OPENx registration for
the add-in to load properly.
As far as I know, the OPENx entries need not be sequential. I.e. you
might have OPEN, OPEN1 and OPEN3 only. In that case your registration
code will overwrite an existing registration, since you are not
looking at the numbers, just counting the entries. I think you
actually have to check which entries are present.
-Govert
On Dec 14, 3:56 pm, andste <a321654...@gmail.com> wrote:
> After a lot of back and forth and testing, I ended up implementing the
> add-in registration in the add-in itself.
> Excel and the registry are interacting in very subtle ways, the
> following code works on Excel version 7, 8, 9, 10, 11, 12 and 14 under
> various scenarios...
>
> Imports Microsoft.Win32
>
> ' ...
>
> Public Class MyIExcelAddIn
> Implements IExcelAddIn
>
> ' ...
>
> Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
>
> ' ...
>
> Dim MyKey1 As RegistryKey ' Key 1 refers to the "Options" subkey
> Dim MyKey2 As RegistryKey ' Key 2 refers to the "Add-in
> Manager"
>
> Dim SubKeyName1 As String
> Dim SubKeyName2 As String
>
> Dim i As Integer
> Dim nOPEN As Double = 0
>
> If Version = 7 Then ' registery entries for Office 1995 are differnt
> SubKeyName1 = "Software\Microsoft\Excel\" & Version &".0\Microsoft
> Excel"
> SubKeyName2 = "Software\Microsoft\Excel\" & Version &".0\Add-in
> Manager"
> Else
> If Version = 8 Then ' registery entries for Office 1997 are somewhat
> different
> SubKeyName1 = "Software\Microsoft\Office\" & Version &".0\Excel
> \Microsoft Excel"
> Else
> SubKeyName1 = "Software\Microsoft\Office\" & Version &".0\Excel
> \Options"
> End If
> SubKeyName2 = "Software\Microsoft\Office\" & Version &".0\Excel\Add-
> in Manager"
> End If
>
> MyKey1 = Registry.CurrentUser.OpenSubKey(SubKeyName1, True)
> If MyKey1 Is Nothing Then ' Keys can be missing for several reasons
> Registry.CurrentUser.CreateSubKey(SubKeyName1)
> MyKey1 = Registry.CurrentUser.OpenSubKey(SubKeyName1, True)
> End If
> MyKey2 = Registry.CurrentUser.OpenSubKey(SubKeyName2, True)
> If MyKey2 Is Nothing Then ' Keys can be missing for several reasons
> Registry.CurrentUser.CreateSubKey(SubKeyName2)
> MyKey2 = Registry.CurrentUser.OpenSubKey(SubKeyName2, True)
> End If
>
> Dim ValueNames1() As String = MyKey1.GetValueNames()
> Dim IsOpen As Boolean = false
> For i = LBound(ValueNames1) To UBound(ValueNames1)
> If ValueNames1(i).StartsWith("OPEN") Then
> nOPEN += 1 ' count the number of active add-ins
> If MyKey1.GetValue(ValueNames1(i)).ToString.Contains("MyAddIn.xll")
> Then
> IsOpen = true
> End If
> End If
> Next
>
> Dim ValueNames2() As String = MyKey2.GetValueNames()
> Dim IsInAddInManager As Boolean = false
> For i = LBound(ValueNames2) To UBound(ValueNames2)
> If ValueNames2(i).Contains("MyAddIn.xll") Then
> IsInAddInManager = true
> End If
> Next
>
> ' Register and activate the add-in
>
> If (Not IsInAddInManager) And (Not IsOpen) then
> If nOPEN = 0 Then
> MyKey1.SetValue("OPEN", "/R """& Path & "MyAddIn.xll""")
> Else
> MyKey1.SetValue("OPEN" & nOPEN, "/R """& Path & "MyAddIn.xll""")
> End If
> MyKey1.Close()
> MyKey2.SetValue(Path & "ApaLibNET.xll", String.Empty)
> MyKey2.Close()
> End If
>
> ' ...
>
> End Sub
>
> ' ...
>
> End Class
andste
> under the understanding that you only need the OPENx registration for
> the add-in to load properly.
in the Add-in Manager.
But you're right, setting OPENx is enough. Just don't forget
RegistryKey.Close()!
> As far as I know, the OPENx entries need not be sequential. I.e. you
> might have OPEN, OPEN1 and OPEN3 only. In that case your registration
> code will overwrite an existing registration, since you are not
> looking at the numbers, just counting the entries. I think you
> actually have to check which entries are present.
In Excel 2010, OPENx seem to get renumbered whenever Excel is started
up, so my counting trick works.
I just had a look at 2003, and you are right, non-sequential OPENx
entries are possible.
Best,
Andi
andste
Dim Path As String = Mid(XlCall.Excel(XlCall.xlGetName),
1,Len(XlCall.Excel(XlCall.xlGetName))-Len("MyAddIn.xll"))
Dim SubKeyName As String
If Version = 7 Then ' registery entries for Office 1995 are different
SubKeyName = "Software\Microsoft\Excel\" & Version &".0\Microsoft
Excel"
Else
If Version = 8 Then ' registery entries for Office 1995 are
different
SubKeyName = "Software\Microsoft\Office\" & Version &".0\Excel
\Microsoft Excel"
Else
SubKeyName = "Software\Microsoft\Office\" & Version &".0\Excel
\Options"
End If
End If
Dim MyKey As RegistryKey
MyKey = Registry.CurrentUser.OpenSubKey(SubKeyName, True)
If MyKey Is Nothing Then ' if key doesn't exist, then create it
Registry.CurrentUser.CreateSubKey(SubKeyName)
MyKey = Registry.CurrentUser.OpenSubKey(SubKeyName, True)
End If
Dim i As Integer
Dim ValueNames() As String = MyKey.GetValueNames()
Dim IsOpen As Boolean = False
Dim maxOPEN As Integer = -1
Dim newOPEN As Integer
For i = LBound(ValueNames) To UBound(ValueNames)
If ValueNames(i).StartsWith("OPEN") Then
If ValueNames(i).ToString = "OPEN" Then
newOPEN = 0
Else
newOPEN = Convert.ToInt32(ValueNames(i).Substring(4))
End If
If newOPEN > maxOPEN Then maxOPEN = newOPEN
If MyKey.GetValue(ValueNames(i)).Contains("MyAddIn.xll") Then
IsOpen = true
End If
End If
Next
If Not IsOpen then
If maxOPEN = -1 Then
MyKey.SetValue("OPEN", "/R """& Path & "MyAddIn.xll""")
Else
MyKey.SetValue("OPEN" & (maxOPEN+1), "/R """& Path &
"MyAddIn.xll""")
End If
MyKey.Close()
End If