"Robert Schwenn" wrote in message
Peter T wrote:
"Robert Schwenn" wrote in message
Peter T wrote:

<snip

If the user can be persuaded to manually add the Com-Addin registration
occurs automatically. Job done. The normal way to distribute a Com-Addin
is
with an Installer. Thinking aloud, as in I haven't done it but I don't
see
why not, your VBA could do the following:

1. Check if the ComAddin is installed
attempt to reference the ComAddin, if not -

2. register the dll Shell Regsrv32
That's doable although there's a fair bit involved overall

I suspect that this step needs admin rights ...

Generally that would require proactive blocking by the administrator,
typically it works (see re Vista below).

Why not test for yourself. Before running the following, uninstall the
ComAddin from Excel (Tools ComAddins), Quit & Restart Excel. Toggle
bUnREgister to un/install. Hope what I've hardcoded to sCls & sFile are
correct, taken from an early example of Chip's addin.

Sub TestRegServ32()
Dim bIsReg As Boolean
Dim sFile As String
Dim sFullDllName As String
Dim sPath As String
Dim sCls As String
' Manually uninstall the ComaDDin from Tools ComAddins before
' running this test or you'll get inconclusive reustls

sPath = "c:\Path-to-the-dll\"

' the public connect class in Chip Pearson's CommAddin
sCls = "TestExcelShutdown.ExcelConnect"
sFile = "TestExcelShutdown.dll"

sFile = Chr(34) & sPath & sFile & Chr(34)

bUnREgister = False
If bUnREgister Then
sFile = sFile & " /u" ' add the unregister flag
End If

vRet = Shell("Regsvr32 /s " & sFile)

bIsReg = DllIsReg(sCls)

MsgBox bIsReg
End Sub

Function DllIsReg(sClsName As String) As Boolean
' sClsName a public class in the dll

Dim oComDll As Object

On Error Resume Next
Set oComDll = CreateObject(sClsName)

DllIsReg = Not oComDll Is Nothing

End Function

Above is a stripped down version of something I have to (un)install a dll as
required. I wouldn't test the dll is registered each time (say in wb open)
by running DllIsReg, instead wait for some call to the dll to fail then do
it.

And I understand Your answer so, that a ComAddin can't be used before it
is
registered on the system.

That's certainly my understanding but normally Regsvr32 works - except I
can't get it work in a non-administrator's account in Vista!

A bit more - contrary to what I speculated previously (see below), even
having registered the dll, added the correct registry entries, doing the
ComAddins.Update will add the addin to the collection but it will not cause
the addin to load. I assume there is a way to programatically load it but
not sure how. In effect it would mean waiting for the next time Excel starts
for the addin to auto load.


3. Add registry entries
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\ Addins\MyAddin.Connect
and four sub keys

4. Application.COMAddIns.Update

Steps 2 & 3 are what an installer would do, having enuserd there are no
running instances of Excel.



Regards,
Peter T