Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem implementing connection point sink
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.InterfaceIsIDispat ch), GuidAttribute("00024413-0000-0000-C000-000000000046") _ Public Interface DExcelApplicationEvents10 <DispId(&H61D) _ Sub NewWorkbook(ByVal wb As Workbook) <DispId(&H616) _ Sub SheetSelectionChange(<MarshalAs(UnmanagedType.IDis patch) ByVal Sh As Object, ByVal Target As Range) <DispId(&H617) _ Sub SheetBeforeDoubleClick(<MarshalAs(UnmanagedType.ID ispatch) ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean) <DispId(&H618) _ Sub SheetBeforeRightClick(<MarshalAs(UnmanagedType.IDi spatch) 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.IDis patch) ByVal Sh As Object, ByVal Target As Hyperlink) <DispId(&H86D) _ Sub SheetPivotTableUpdate(<MarshalAs(UnmanagedType.IDi spatch) 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("SheetBeforeDou bleClick") 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("SheetBeforeRig htClick") 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("SheetDeactivat e") End Sub Public Overridable Sub SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Excel.Hyperlink) Implements DExcelApplicationEvents10.SheetFollowHyperlink System.Diagnostics.Debug.WriteLine("SheetFollowHyp erlink") End Sub Public Overridable Sub SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As Excel.PivotTable) Implements DExcelApplicationEvents10.SheetPivotTableUpdate System.Diagnostics.Debug.WriteLine("SheetPivotTabl eUpdate") End Sub Public Overridable Sub SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Implements DExcelApplicationEvents10.SheetSelectionChange System.Diagnostics.Debug.WriteLine("SheetSelection Change") 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("WindowDeactiva te") 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("WorkbookActiva te") End Sub Public Overridable Sub WorkbookAddinInstall(ByVal Wb As Excel.Workbook) Implements DExcelApplicationEvents10.WorkbookAddinInstall System.Diagnostics.Debug.WriteLine("WorkbookAddinI nstall") End Sub Public Overridable Sub WorkbookAddinUninstall(ByVal Wb As Excel.Workbook) Implements DExcelApplicationEvents10.WorkbookAddinUninstall System.Diagnostics.Debug.WriteLine("WorkbookAddinU ninstall") End Sub Public Overridable Sub WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Implements DExcelApplicationEvents10.WorkbookBeforeClose System.Diagnostics.Debug.WriteLine("WorkbookBefore Close") End Sub Public Overridable Sub WorkbookBeforePrint(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Implements DExcelApplicationEvents10.WorkbookBeforePrint System.Diagnostics.Debug.WriteLine("WorkbookBefore Print") 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("WorkbookBefore Save") End Sub Public Overridable Sub WorkbookDeactivate(ByVal Wb As Excel.Workbook) Implements DExcelApplicationEvents10.WorkbookDeactivate System.Diagnostics.Debug.WriteLine("WorkbookDeacti vate") End Sub Public Overridable Sub WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As Object) Implements DExcelApplicationEvents10.WorkbookNewSheet System.Diagnostics.Debug.WriteLine("WorkbookNewShe et") 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.WorkbookPivotTableCloseC onnection System.Diagnostics.Debug.WriteLine("WorkbookPivotT ableCloseConnection") End Sub Public Overridable Sub WorkbookPivotTableOpenConnection(ByVal Wb As Excel.Workbook, ByVal Target As Excel.PivotTable) Implements DExcelApplicationEvents10.WorkbookPivotTableOpenCo nnection System.Diagnostics.Debug.WriteLine("WorkbookPivotT ableOpenConnection") End Sub End Class -------------------------------------- Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem implementing connection point sink
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem implementing connection point sink
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/d...displaylang=en Hope this helps, Nick "Chris Peacock" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem implementing connection point sink
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/newsgroups...5-bfd7a7bb6757 "Chris Peacock" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem implementing connection point sink
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data connection and formatting problem | Excel Discussion (Misc queries) | |||
Double Bowl Undermount Kitchen Sink | Excel Worksheet Functions | |||
Bathroom Sink Plug | Excel Worksheet Functions | |||
Problem getting external data using an existing connection | Excel Discussion (Misc queries) | |||
Problem implementing SolverAdd - Can anyone solve? | Excel Programming |