Compartilhar via

Excel OLE Embedding Errors if you have Managed Add-In Sinking Application Events in Excel

  • Artigo

Summary

If you build a managed component that loads into Excel's process space, and the managed code uses the PIA (or a custom IA using event delegates)  to sink events on the Application object, that Excel instance may encounter problems when used for OLE embedding.  The problems include:

  • Being unable to update object if it is edited twice (inplace activatted, edited, closed, then activated and edited again) between saves.
  • Failure to release lock on object storage, which may inhibit proper closing of the main document file or return an error.
  • Crash of Word when one of the above problems occur and user attempts to edit object again after the reported error.

This problem occus in all current versions of Excel (2002, 2003, and 2007).

Cause

The problem is the result on conflicting design behaviors between OLE and .NET over object lifetime. For OLE embedding, Excel uses an in-process handler (InprocHandler) which acts as a proxy to the embedded COM object.  Excel is desgined to keep a workbook object alive based on the reference count to that object, and the OLE handler expects that its final release of the object will free the object when it is done.  If you introduce a managed addin or component into Excel that sinks the Application event, the delegate handlers .NET uses for the events may artificailly increase the reference count on the workbook objects used by OLE. This occurs because many events (like WorkbookOpen, WorkbookActivate, WorkbookBeforeSave) pass a Workbook object as a parameter in the event, and these events can fire for OLE embedded workbooks as well as non-OLE workbooks.  The .NET Framework uses garbage collection (GC) instead of reference counting, so the release of references cannot be predicted and may not occur for several minutes (or hours, or never in some cases), thereby keeping these OLE object alive when the host application intended them to be closed.  This can cause problems for OLE32 (which does not know about the .NET addin) when it attempts to create a new object and bind to the same storage used by the previous object that did not close. 

The crash in Word is caused by a bug in Word (2003 and 2007) that keeps a bad pointer after one of the errors.  That bug is being investigated for a future fix, but will only address the crash, not the actual OLE error that prevents the editing of the object.

Status

The problem is a design conflict between OLE (which expects deterministic reference count) and .NET (which uses non-deterministic GC). The issue has been investigated by the Excel, OLE, and .NET development teams, but no solution has been found to resolve this issue. Instead, it has been determined that the addin/component developer will need to ensure that they release references to OLE objects when touched by their managed code.

Resolution

To resolve the problem, the addin component needs to change how it handles events. As the managed code developer, you should do the following things:

  1. Unfortunately, .NET delegates complicate matters because they can (and often do) run on secondary threads that requrie the Workbook objects to be marshaled between threads. This can create a proxy object, which can keep a yet another reference on the workbook object. To avoid that, it is suggested that you avoid using delegates (either directly, or via WithEvents if in VB.NET), and instead use a direct event sink which you can connect by ComTypes.IConnectionPointContainer. To do this, you will need to implement a stub of all the events on the Application object, even if you not plan to use all those events.
  2. From inside your stub function, call Marshal.ReleaseComObject on each Workbook object passed as a parameter to the event. This will free the reference .NET adds to the object when handing the event.

If the references are released when the event completes, the problems mentioned above will not occur.

More Information

Attached is a Visual Studio 2005 C# sample COM Add-In project that demonstrates the resolution in full.  It uses a reusable C# class object to make it easier to port that file into your own application and sink events off the wrapper instead of the Excel Application object directly. It also provides an option to filter out embedded workbook events so that you don't run more code that keep an unnecessary reference count on those objects. If you choose to allow such events to fire on delegates, you need to make sure your delegate code also calls Marshal.ReleaseComObject on such workbook objects.

Steps to Reproduce the Problem

  1. Start Visual Studio 2005 and create a Shared COM Addin project for Excel.  Set the project to load on Excel startup.
  2. Add a reference to the Excel PIA.  In the OnConnection function, add a delegate for the OpenWorkbook event on the Application object you are given. In your OpenWorkbook event routine, add code to display a message box.
  3. Compile the project and start Excel. Make sure when you open an existing workbook your message box displays.
  4. Add some data in the currently open Excel workbook and copy it to the clipboard.
  5. Start Word and choose Edit | Paste Special (or dropdown menu under Paste button in Word 2007, and Paste Special).  In the Paste Special dialog, choose Microsoft Office Excel Workbook Object. The object should insert.
  6. Double-click the embedded Excel object to attempt to edit it. 

 An error dialog appears stating the object cannot be edited.  If the user attempts to edit the object again, they may crash Word.  Using the sample addin attached here, the problem does not occur because of the workaround.

 

xlEvents_Sample.zip

Comments

  • Anonymous
    April 11, 2008
    The comment has been removed