Problem implementing connection point sink

[Chris Peacock]

In order to solve the problem of Cancel event parameters not working
using VS.NET 2003 and Office XP, I've attempted to implement a
connection point sink for Excel application events as described in
Knowledge Base article 830519, using VB.NET. I've made this work OK
for Word, but when I implement the fix for Excel, the line
"m_oConnectionPoint.Advise(Me, m_Cookie)" in SetupConnection (code
shown below) throws an InvalidCastException with the message
"Interface not supported". I've thoroughly checked my definition of
the application events interface and cannot see a problem with it. I'm
using VS.NET 2003, .NET framework 1.1.4322, and Office XP Pro.

Has anyone else managed this successfully? I'd really appreciate some
help with this.

My code for the sink class is as follows:-

Imports System
Imports System.Runtime.InteropServices
Imports Excel

#Region "Interfaces"
<InterfaceType(ComInterfaceType.InterfaceIsIDispatch),
GuidAttribute("00024413-0000-0000-C000-000000000046")> _
Public Interface DExcelApplicationEvents10
<DispId(&H61D)> _
Sub NewWorkbook(ByVal wb As Workbook)
<DispId(&H616)> _
Sub SheetSelectionChange(<MarshalAs(UnmanagedType.IDispatch)>
ByVal Sh As Object, ByVal Target As Range)
<DispId(&H617)> _
Sub SheetBeforeDoubleClick(<MarshalAs(UnmanagedType.IDispatch)>
ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean)
<DispId(&H618)> _
Sub SheetBeforeRightClick(<MarshalAs(UnmanagedType.IDispatch)>
ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean)
<DispId(&H619)> _
Sub SheetActivate(<MarshalAs(UnmanagedType.IDispatch)> ByVal Sh As
Object)
<DispId(&H61A)> _
Sub SheetDeactivate(<MarshalAs(UnmanagedType.IDispatch)> ByVal Sh
As Object)
<DispId(&H61B)> _
Sub SheetCalculate(<MarshalAs(UnmanagedType.IDispatch)> ByVal Sh
As Object)
<DispId(&H61C)> _
Sub SheetChange(<MarshalAs(UnmanagedType.IDispatch)> ByVal Sh As
Object, ByVal Target As Range)
<DispId(&H61F)> _
Sub WorkbookOpen(ByVal Wb As Workbook)
<DispId(&H620)> _
Sub WorkbookActivate(ByVal Wb As Workbook)
<DispId(&H621)> _
Sub WorkbookDeactivate(ByVal Wb As Workbook)
<DispId(&H622)> _
Sub WorkbookBeforeClose(ByVal Wb As Workbook, ByRef Cancel As
Boolean)
<DispId(&H623)> _
Sub WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, ByRef Cancel As Boolean)
<DispId(&H624)> _
Sub WorkbookBeforePrint(ByVal Wb As Workbook, ByRef Cancel As
Boolean)
<DispId(&H625)> _
Sub WorkbookNewSheet(ByVal Wb As Workbook,
<MarshalAs(UnmanagedType.IDispatch)> ByVal Sh As Object)
<DispId(&H626)> _
Sub WorkbookAddinInstall(ByVal Wb As Workbook)
<DispId(&H627)> _
Sub WorkbookAddinUninstall(ByVal Wb As Workbook)
<DispId(&H612)> _
Sub WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
<DispId(&H614)> _
Sub WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
<DispId(&H615)> _
Sub WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
<DispId(&H73E)> _
Sub SheetFollowHyperlink(<MarshalAs(UnmanagedType.IDispatch)>
ByVal Sh As Object, ByVal Target As Hyperlink)
<DispId(&H86D)> _
Sub SheetPivotTableUpdate(<MarshalAs(UnmanagedType.IDispatch)>
ByVal Sh As Object, ByVal Target As PivotTable)
<DispId(&H870)> _
Sub WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal
Target As PivotTable)
<DispId(&H871)> _
Sub WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal
Target As PivotTable)
End Interface
#End Region

'Custom connection point sink for Excel events (See Microsoft
Knowledge Base Article 830519)
Public Class ExcelAppEventHelper
Implements DExcelApplicationEvents10, IDisposable

#Region "Variables"
Private app As Excel.Application
Private m_oConnectionPoint As UCOMIConnectionPoint
Private m_Cookie As Integer
#End Region

#Region "Properties"
Public ReadOnly Property Application() As Excel.Application
Get
Return app
End Get
End Property
#End Region

Public Sub New()
m_oConnectionPoint = Nothing
m_Cookie = 0
End Sub

Public Sub SetupConnection(ByVal app As Excel.Application)
Dim guid As Guid
Dim oConnPointContainer As UCOMIConnectionPointContainer

If m_Cookie <> 0 Then Return

Me.app = app

' GUID of the DIID_ApplicationEvents dispinterface.
guid = New Guid("{00024413-0000-0000-C000-000000000046}")
' QI for IConnectionPointContainer.
oConnPointContainer = CType(app,
UCOMIConnectionPointContainer)
' Find the connection point and then advise.
oConnPointContainer.FindConnectionPoint(guid,
m_oConnectionPoint)
m_oConnectionPoint.Advise(Me, m_Cookie)
End Sub

Public Sub RemoveConnection()
If m_Cookie <> 0 Then
m_oConnectionPoint.Unadvise(m_Cookie)
m_oConnectionPoint = Nothing
m_Cookie = 0
End If
End Sub

Public Overridable Sub Dispose() Implements IDisposable.Dispose
RemoveConnection()
End Sub

Public Overridable Sub NewWorkbook(ByVal wb As Excel.Workbook)
Implements DExcelApplicationEvents10.NewWorkbook
System.Diagnostics.Debug.WriteLine("NewWorkbook")
End Sub

Public Overridable Sub SheetActivate(ByVal Sh As Object)
Implements DExcelApplicationEvents10.SheetActivate
System.Diagnostics.Debug.WriteLine("SheetActivate")
End Sub

Public Overridable Sub SheetBeforeDoubleClick(ByVal Sh As Object,
ByVal Target As Excel.Range, ByRef Cancel As Boolean) Implements
DExcelApplicationEvents10.SheetBeforeDoubleClick
System.Diagnostics.Debug.WriteLine("SheetBeforeDoubleClick")
End Sub

Public Overridable Sub SheetBeforeRightClick(ByVal Sh As Object,
ByVal Target As Excel.Range, ByRef Cancel As Boolean) Implements
DExcelApplicationEvents10.SheetBeforeRightClick
System.Diagnostics.Debug.WriteLine("SheetBeforeRightClick")
End Sub

Public Overridable Sub SheetCalculate(ByVal Sh As Object)
Implements DExcelApplicationEvents10.SheetCalculate
System.Diagnostics.Debug.WriteLine("SheetCalculate")
End Sub

Public Overridable Sub SheetChange(ByVal Sh As Object, ByVal
Target As Excel.Range) Implements
DExcelApplicationEvents10.SheetChange
System.Diagnostics.Debug.WriteLine("SheetChange")
End Sub

Public Overridable Sub SheetDeactivate(ByVal Sh As Object)
Implements DExcelApplicationEvents10.SheetDeactivate
System.Diagnostics.Debug.WriteLine("SheetDeactivate")
End Sub

Public Overridable Sub SheetFollowHyperlink(ByVal Sh As Object,
ByVal Target As Excel.Hyperlink) Implements
DExcelApplicationEvents10.SheetFollowHyperlink
System.Diagnostics.Debug.WriteLine("SheetFollowHyperlink")
End Sub

Public Overridable Sub SheetPivotTableUpdate(ByVal Sh As Object,
ByVal Target As Excel.PivotTable) Implements
DExcelApplicationEvents10.SheetPivotTableUpdate
System.Diagnostics.Debug.WriteLine("SheetPivotTableUpdate")
End Sub

Public Overridable Sub SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Excel.Range) Implements
DExcelApplicationEvents10.SheetSelectionChange
System.Diagnostics.Debug.WriteLine("SheetSelectionChange")
End Sub

Public Overridable Sub WindowActivate(ByVal Wb As Excel.Workbook,
ByVal Wn As Excel.Window) Implements
DExcelApplicationEvents10.WindowActivate
System.Diagnostics.Debug.WriteLine("WindowActivate")
End Sub

Public Overridable Sub WindowDeactivate(ByVal Wb As
Excel.Workbook, ByVal Wn As Excel.Window) Implements
DExcelApplicationEvents10.WindowDeactivate
System.Diagnostics.Debug.WriteLine("WindowDeactivate")
End Sub

Public Overridable Sub WindowResize(ByVal Wb As Excel.Workbook,
ByVal Wn As Excel.Window) Implements
DExcelApplicationEvents10.WindowResize
System.Diagnostics.Debug.WriteLine("WindowResize")
End Sub

Public Overridable Sub WorkbookActivate(ByVal Wb As
Excel.Workbook) Implements DExcelApplicationEvents10.WorkbookActivate
System.Diagnostics.Debug.WriteLine("WorkbookActivate")
End Sub

Public Overridable Sub WorkbookAddinInstall(ByVal Wb As
Excel.Workbook) Implements
DExcelApplicationEvents10.WorkbookAddinInstall
System.Diagnostics.Debug.WriteLine("WorkbookAddinInstall")
End Sub

Public Overridable Sub WorkbookAddinUninstall(ByVal Wb As
Excel.Workbook) Implements
DExcelApplicationEvents10.WorkbookAddinUninstall
System.Diagnostics.Debug.WriteLine("WorkbookAddinUninstall")
End Sub

Public Overridable Sub WorkbookBeforeClose(ByVal Wb As
Excel.Workbook, ByRef Cancel As Boolean) Implements
DExcelApplicationEvents10.WorkbookBeforeClose
System.Diagnostics.Debug.WriteLine("WorkbookBeforeClose")
End Sub

Public Overridable Sub WorkbookBeforePrint(ByVal Wb As
Excel.Workbook, ByRef Cancel As Boolean) Implements
DExcelApplicationEvents10.WorkbookBeforePrint
System.Diagnostics.Debug.WriteLine("WorkbookBeforePrint")
End Sub

Public Overridable Sub WorkbookBeforeSave(ByVal Wb As
Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean)
Implements DExcelApplicationEvents10.WorkbookBeforeSave
System.Diagnostics.Debug.WriteLine("WorkbookBeforeSave")
End Sub

Public Overridable Sub WorkbookDeactivate(ByVal Wb As
Excel.Workbook) Implements
DExcelApplicationEvents10.WorkbookDeactivate
System.Diagnostics.Debug.WriteLine("WorkbookDeactivate")
End Sub

Public Overridable Sub WorkbookNewSheet(ByVal Wb As
Excel.Workbook, ByVal Sh As Object) Implements
DExcelApplicationEvents10.WorkbookNewSheet
System.Diagnostics.Debug.WriteLine("WorkbookNewSheet")
End Sub

Public Overridable Sub WorkbookOpen(ByVal Wb As Excel.Workbook)
Implements DExcelApplicationEvents10.WorkbookOpen
System.Diagnostics.Debug.WriteLine("WorkbookOpen")
End Sub

Public Overridable Sub WorkbookPivotTableCloseConnection(ByVal Wb
As Excel.Workbook, ByVal Target As Excel.PivotTable) Implements
DExcelApplicationEvents10.WorkbookPivotTableCloseConnection
System.Diagnostics.Debug.WriteLine("WorkbookPivotTableCloseConnection")
End Sub

Public Overridable Sub WorkbookPivotTableOpenConnection(ByVal Wb
As Excel.Workbook, ByVal Target As Excel.PivotTable) Implements
DExcelApplicationEvents10.WorkbookPivotTableOpenConnection
System.Diagnostics.Debug.WriteLine("WorkbookPivotTableOpenConnection")
End Sub
End Class

 

[Chris Peacock]

Hi Nick,

Thanks for posting that for me. Guess what? It still does the same
thing even when I use your C# code!

There are a couple of things I had to change to get it to work that
far however.

It wouldn't compile for me with "using
Excel=Microsoft.Office.Interop.Excel;", I had to change this to just
"using Excel;" or it complained that "Namespace '' already contains a
definition for 'Excel'". What version of the Excel library are you
using, and how did you add the reference?

The other thing was that you have the parameter to SetupConnection
defined as 'Excel.ApplicationClass'. How do you get the value to pass
to this? I was using 'Excel.Application' (the interface) and if I
change it to 'Excel.ApplicationClass' I get an invalid cast error when
I call this from my main add-in class, which uses the 'application'
parameter passed to the 'OnConnection' function. How are you calling
SetupConnection, are you building a standard COM add-in?

Thanks very much for your help.

Chris.

 

[Guest]

Chris,

Are you referencing the Office Primary Interop Assemblies in your code ? If not I think you should.

FYI I am using...

Microsoft Excel 2002 (10.4302.4219) SP-2
Visual Studio 2003 v7.1.3088
Microsoft .NET Framework 1.1 v1.1.4322

I obtained the Office XP Primary Interop Assemblies from the following link

http://www.microsoft.com/downloads/...1e-3060-4f71-a6b4-01feba508e52&displaylang=en

Hope this helps,

Nick

 

[Guest]

Chris,

Check out this post which sorted out all my problems.

Eseentially you need to apply the [ClassInterface(ClassInterfaceType.None)] attribute to your helper class in order to stop it creating a default interface.

http://msdn.microsoft.com/newsgroup...exp=&mid=93019250-aa11-450b-add5-bfd7a7bb6757

 

[Chris Peacock]

Hi Nick,

I've finally sorted it! I tried all the things you suggested, but still
the same result...

However, another difference with my code is that the class that I'm
actually instantiating as the sink is derived from the class that
implements the interface (to allow this to be reusable) - the problem
was that this class was protected rather than public! Don't ask me why
this matters, the same principle with Word and a non-public class works
fine.

I got rid of the DispIDs in the class definition and also the
'[ClassInterface(ClassInterfaceType.None)]', and now it still works.

Thanks very much for all your help though!

Regards,
Chris.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!