PRB: Error Using WithEvents or Delegates to Handle Excel Events from Visual Basic .NET or Visual C# .NET

Article translations Article translations
Article ID: 316653 - View products that this article applies to.
This article was previously published under Q316653
Expand all | Collapse all

SYMPTOMS

When an application tries to handle events from Excel by using WithEvents (in Visual Basic .NET) or Delegates (in Visual Basic .NET or Visual C# .NET), you may receive the following error message:
An unhandled exception of type 'System.InvalidCastException' occurred in interop.excel.dll

Additional information: No such interface supported

CAUSE

The event wrapper classes in the interop assembly that is generated for Excel have access privileges that are too strict. Because these wrapper classes are marked Private, the universal runtime disallows an IUnknown::QueryInterface call to the wrapper classes and returns E_NOINTERFACE. This translates to the exception error that is described in the "Symptoms" section.

RESOLUTION

To work around this problem, you can manually modify the interop assembly that is generated for Excel to relax the access privileges for the event wrapper classes. To do this, follow these steps:
  1. Save and close your Visual Studio .NET project.
  2. Open a Visual Studio .NET command prompt and change the directory to the output directory of your project (for example, project name\bin for a Visual Basic project or project name\bin\release for a C# project).
  3. Use Ildasm.exe to extract the intermediate language from the Excel interop assembly, as follows:
    ildasm.exe /source Interop.Excel.dll /output=Interop.Excel.il
    					
  4. Open Interop.Excel.il in a text editor such as WordPad and search for occurrences of "_SinkHelper". Change the access privileges of the _SinkHelper classes from Private to Public, and then save and close Interop.Excel.il.
  5. At the Visual Studio .NET command prompt, use Ilasm.exe with the /dll switch to recompile the intermediate language file into an interop assembly, as follows:
    ilasm.exe /dll Interop.Excel.il /output=Interop.Excel.dll
    					
  6. Open your project in Visual Studio .NET.
  7. Add a reference to the Interop.Excel.dll that you created in step 5. To do this, follow these steps:

    IMPORTANT: These steps are required. If you do not set a reference to the new interop assembly, Visual Studio .NET regenerates the interop assembly when you compile the project or create a setup package.
    1. In Visual Studio .NET Solution Explorer, right-click Excel in the list of project references, and then click Remove.
    2. On the Project menu, click Add Reference.
    3. On the .NET tab, click Browse, locate the Interop.Excel.dll file in the output directory of your project, and then click Open.
    4. Click OK in the Add References dialog box to accept your selection.
  8. Test the program again with the modified interop assembly. To do this, follow these steps:
    1. Press F5 to rebuild and to run the program.
    2. When Form1 appears, click Button1 to start Excel.
    3. Start a new workbook, and then enter data in any cell.
    4. On the View menu in Visual Studio .NET, click Other Windows and then click Output. The "Change Event Triggered" text in the Output window confirms that your program handled the event.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Start Visual Studio .NET.
  2. On the File menu, click New, and then click Project.
  3. Under Visual Basic Projects, click Windows Application, and then click OK. By default, Form1 is created.
  4. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel Object Library, and then click Select.

      NOTE: The Excel object library contains a version number. The version for Excel 2000 is 9.0; the version for Excel 2002 is 10.0.
    3. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.
  5. Add a command button to Form1.
  6. Double-click Button1 to edit the Click event handler.
  7. Replace the following code
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
        End Sub
    					
    with:
        Dim WithEvents oXL As Excel.Application
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            oXL = New Excel.Application()
            oXL.Visible = True
            oXL.UserControl = True
        End Sub
    
        Private Sub oXL_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Handles oXL.SheetChange
            Debug.WriteLine("Change Event Triggered")
        End Sub
    					
  8. Press F5 to build and to run the program.
  9. After Form1 loads, click Button1. Notice that you receive the error message on the following line:
    oXL = New Excel.Application()
    					

REFERENCES

For additional information about handling events in Excel, click the following article numbers to view the articles in the Microsoft Knowledge Base:
302814 HOW TO: Handle Events for Excel by Using Visual Basic .NET
302815 HOW TO: Handle Events for Microsoft Excel by Using Microsoft Visual C# .NET

Properties

Article ID: 316653 - Last Review: January 30, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Studio .NET 2002 Professional Edition
Keywords: 
kbautomation kberrmsg kbprb KB316653

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com