Excel COM Add-in must handle workbook open event

[Maurizio BELLANTONE]

In an already working well VB6 written COM addin, in order to obtain that my
COM Add-in intercept WorkbookOpen event I added these lines to the
Connect.dsr code:

***
Dim oMyApp As New ClassXlApp
....
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom()
As Variant)
On Error Resume Next

Set oXl = Application
Set oMyApp.App = Application
....
***

Instead ClassXlApp contains:

***
Public WithEvents App As Excel.Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim sExt As String

On Error GoTo Linea12
MsgBox "ClassXlApp - You open a sheet!!!"
Linea12:
On Error GoTo 0
End Sub
***

but no message box appear.
What I forget to add to my code ?
TIA,
Maurizio

=========================================================
BELLANTONE MAURIZIO - Teacher, IT Consultant, Programmer
SPINETTA MARENGO (AL) - ITALY

 

[XL-Dennis]

Maurizio,

The following works in that when adding a new worksheet:

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_NewWorkbook(ByVal Wb As Excel.Workbook)
MsgBox "New Workbook addedd!"
End Sub

Private Sub xlApp_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As
Object)
MsgBox "New Worksheet added!"
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "A workbook has been opened!"
End Sub

 

[Cindy M.]

Hi Maurizio,

I suggest you start by taking out "On Error Resume Next" and "On Error GoTo
Linea 12" and replace it with proper error handling so that you can see what
errors are being generated. That will help you track down any problems.
Skipping errors may seem the easy way to do things, but errors are usually
there for a reason - to tell you what's going wrong.

In an already working well VB6 written COM addin, in order to obtain that my
COM Add-in intercept WorkbookOpen event I added these lines to the
Connect.dsr code:

***
Dim oMyApp As New ClassXlApp
....
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom()
As Variant)
On Error Resume Next

Set oXl = Application
Set oMyApp.App = Application
....
***

Instead ClassXlApp contains:

***
Public WithEvents App As Excel.Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim sExt As String

On Error GoTo Linea12
MsgBox "ClassXlApp - You open a sheet!!!"
Linea12:
On Error GoTo 0
End Sub
***

but no message box appear.
What I forget to add to my code ?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Maurizio BELLANTONE]

Thank you. I resolved.
Removing "On Error" statements did not modify add-in execution because no
error event occurs: cause of the problem was incomplete uninstallation of
add-in previous versions from the VM used to test it.
After a manual clean of some registry keys, after a new setup add-in works
better.

BUT there is another problem: only users with administrators privileges can
use it.
A MS support document (
http://support.microsoft.com/default.aspx?scid=kb;EN-US;290868 ) state that
setting registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\MyAddin.Connect

value to 3 must resolve, but it didn't.
Regards,

Maurizio

=========================================================
BELLANTONE MAURIZIO - Teacher, IT Consultant, Programmer
SPINETTA MARENGO (AL) - ITALY

 

[XL-Dennis]

Maurizio

I'm not sure that I understand the issue. In general using the HKLM registry
always require administrative priviligies while HKCU not.

 

[Maurizio BELLANTONE]

On the VM I use for testing, I install add-in as Administrator and, as
Administrator, add-in works.
But if I try to use it as non-administrator user Excel don't load it at
startup.
In registry, as conseguence of instructions of citated MS Support doc, setup
write the key

HKLM\SOFTWARE\Microsoft\Office\Excel\Addins\MyAddin.Connect\LoadBehavoir = 3

but in

HKCU\Microsoft\Office\Excel\Addins\MyAddin.Connect\LoadBehavoir = 3

there is if user is Administrator, there is not if I log as another user.

Thanks,
Maurizio

 

[XL-Dennis]

Hi Maurizio,

"But if I try to use it as non-administrator user Excel don't load it at
startup."

The HKLM installation is supposed to make the add-in available for all users
(but it require administrative rights to install it). The major drawback is
that HKLM installations are not showed in the COM dialog in Excel...

It sounds that the OnConnection method is never called and I suspect that it
may be related to the installation in the registry.

I assume that the Loadbehavior is a DWORD registry entry with the Decimal
value of 3.