Unable to activate Excel embedding when manged code addin is installed

Symptoms

After sucessfully inserting a Microsoft Excel OLE object into another program document, you may recieve errors that the server is unavailable or the storage is corrupt when attempting to open or edit the OLE object again. The exact error message can vary, since it is generated by the application hosting the embedding and not by Excel. In some cases, the host application may shutdown or crash after the error. Microsoft Word, for example, may crash if you get the error on one attempt to edit, then try to edit the object again after the error.

This problem occurs when the following conditions are true:
  • A managed (.NET) COM Addin, or a Visual Studio Tools for Office Second Edition (VSTO SE) Addin, is registered to load on startup in Excel.
  • The managed code addin is coded to sink events on the Application object using the Primary Interop Assembly (PIA) for Excel, or a custom Interop Assembly (IA) that uses managed delegates.
  • The OLE object being edited has been loaded and activated at least once before successfully. Subsequent activations of that same object are the ones that often encounter the error.

Cause

The problem is caused by an artificial reference count on the OLE object held by the managed addin. This reference count occurs because many of the events on the Application object pass Workbook or Window objects as parameters. These events fire for OLE embedded workbooks the same as non-OLE embedded workbooks. When using the PIA or a standard IA to sink events, these calls are wrapped automatically by the CLR, and the objects are reference counted. Because the CLR uses Garbage Collection (GC), these references are not freed when the event returns. They may be held for several minutes or several hours depending on when and how the GC finally gets around to freeing them. Because of the extra reference count, the OLE object is never properly closed after the first editing. Consequently, when you attempt to edit the OLE object again, you cannot edit it because the object is already open and it's storage is locked and cannot be re-read.

Resolution

The addin developer needs to take into account reference counts for events. So addins that require event sinks should be developed to use a direct connection point to the Application object instead of managed events (delegates). In addition, the addin should explicitly release COM references to parameters passed to these events if they detect the object is an embeddeding.

Status

Microsoft Excel is implemented to follow the rules of OLE, which will require deterministic release of its OLE objects to properly close those objects when requested by the OLE host. This behavior is by design.Current versions of the Common Lanaguage Runtime (CLR) do not support OLE, and will use a non-deterministic cleanup routine (the low priority GC thread) to free COM references on the OLE objects passed into the managed addin. This behavior is by design. Because these two designs are in conflict, the problem can occur if your addin introduces managed code into Excel. Addin developers, therefore, are responsible for understanding this limitation and building their addin to explicitly free COM references if the sink Application events.

More Information

Developers writing a managed addin for Excel should consider
מאפיינים:

מזהה פריט: 944839 - סקירה אחרונה: - תיקון: 1

משוב