Solution: Excel OLE Embedding Errors

[Mad1523]

When attaching to Excel events using the Application object in an
any .NET managed add-in, you may experience a problem when you try to
edit embedded Excel sheets inside Microsoft Word. You will most likely
encounter errors:

"The program used to create this object is Excel. That program is not
installed on your computer. To edit this object, you must install a
program that can open the object."

or

"Microsoft Office Word can't start the application required to open
this object. An error occurred and this feature is no longer
functioning properly. Would you like to repair this feature now?"

As Govert mentioned in this post (http://groups.google.com/group/
exceldna/browse_thread/thread/7374ccad1fae0436), this issue is not
Excel-DNA specific but rather an issue related to the manner in which
OLE and .NET manage object lifetimes. An explanation and solution for
a COM add-in was provided by an Office developer at Microsoft (http://
blogs.msdn.com/b/vsofficedeveloper/archive/2008/04/11/excel-ole-
embedding-errors-with-managed-addin.aspx). However, that code is
specific to a COM add-in and you have to tweak it a bit to use the
solution in an Excel-DNA add-in.

Here is an example of an add-in that will cause the error messages
when editing Excel sheets embedded within Word as we are attaching to
events directly from the Application object. This add-in simply hooks
a few events and shows a MessageBox when the events fire:
https://gist.github.com/1079974

Now, here is the workaround using the code at the MSDN blog (above),
tweaked for use with Excel-Dna. To use the solution, we have to
dynamically create a COM add-in during the AutoOpen() event and then
use the xlEvents class provided at the blog. This add-in hooks the
same events as the above add-in, but does not produce the error
messages within Microsoft Word when editing Excel sheets.

xlEvents.cs: https://gist.github.com/1079987
AddIn.cs: https://gist.github.com/1079996

I've also encountered other strange side effects unrelated to Word
when using delegates directly from the Application object, so I'd
recommend using this workaround in all your add-ins. I thought I would
share this in case it might be helpful for someone.

Enjoy.

[Mad1523]

It turns out that if you set m_xlAppEvents.DisableEventsIfEmbedded to
FALSE, you may still encounter the errors discussed above.

This happens if you extract objects from the Workbook object provided
to events without releasing them. The workaround above releases the
Workbook objects fine, but not other objects you may use. This only
happens if you have m_xlAppEvents.DisableEventsIfEmbedded set to
FALSE. When this is set to TRUE, it does not fire events if the
worksheet is embedded, thus not allowing your code to extract objects
(like Sheets) from the Workbook object. You may want to run Workbook
events in embedded worksheets, however.

For example, this causes errors (m_xlAppEvents.DisableEventsIfEmbedded
= false):

private void xlWorkbookBeforeClose(Excel._Workbook oBook, ref bool
Cancel)
{
foreach (Excel.Worksheet ws in oBook.Sheets)
{
// Blah
}
}

The above code references several objects from the Workbook object:
Sheets and multiple Worksheet objects. The solution is to keep tract
of which objects you reference from the Workbook object provided and
release them when you are done. xlEvents will release the Workbook
object for you. This is your workaround:

private void xlWorkbookBeforeClose(Excel._Workbook oBook, ref bool
Cancel)
{
Excel.Sheets sheets = oBook.Sheets;

foreach (Excel.Worksheet ws in oBook.Sheets)
{
// Blah
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
}

System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);

}

You'll notice that we must keep a reference to Sheets so we can later
release it instead of calling it directly in the foreach statement. As
discussed, the problems result from the .NET managed code not
releasing references to the COM objects. The GC releases those
references eventually, but in the managed environment, we just aren't
sure when that is going to happen and Excel wants those released
immediately.

Hope this helps.

[Govert van Drimmelen]

Hi,

I wonder if you'd like to try NetOffice (http://
netoffice.codeplex.com) instead of the Primary Interop Assemblies.
It seems to work fine with Excel-DNA if you know how to hook it up -
discussed here: http://groups.google.com/group/exceldna/browse_thread/thread/f356a30adf9130d0.

For NetOffice Sebastian generates late-bound wrappers using a custom-
built tool, and I think the generated code includes some extra
lifetime handling.
Even if it doesn't help as is, he might advise a bit or you might be
able to change his code-generator tool to work around this. Anyway,
I'm pretty sure he'd want to know and work around the .NET/COM
embedding issues you are running into.

So I suggest you have a look, and also raise this issue on the
NetOffice Discussion tab with some links and the background you gave
before.

-Govert

[Mad1523]

Govert,

You are right, this does look promising:
http://netoffice.codeplex.com/wikipage?title=Tec_Documentation_English_Management

"If you have already work with Primary Interop Assemblies by
Microsoft, you know that Office is based on a COM-architecture. That
means that you retrieve COM proxy objects in your application. It is
your responsibility to free them again. ... In NetOffice you do not
have to free such objects explictly, you may use them implicitly."

Ideally, we should be freeing our COM objects throughout all of our
code, not just in events. It looks like NetOffice may do this for us.
I will look into it and post back.

Thanks

On Jul 14, 12:36 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> I wonder if you'd like to try NetOffice (http://
> netoffice.codeplex.com) instead of the Primary Interop Assemblies.
> It seems to work fine with Excel-DNA if you know how to hook it up -

> discussed here:http://groups.google.com/group/exceldna/browse_thread/thread/f356a30a....

[Mad1523]

Here's an update on the OLE errors and using NetOffice as the
solution:

Turns out, using NetOffice instead of the interop assemblies solves
the problem encountered above related to COM proxy objects not being
properly disposed of in the xll addin. After porting my code over to
NetOffice, the errors have disappeared. If you are looking to solve
the same problem, just keep track of your proxy count within NetOffice
(attach to LateBindingApi.Core.Factory.ProxyCountChanged) as your code
executes. You want to keep the proxy counts as low as necessary.
Additionally, I run

XL.DisposeChildInstances(false);

in the WorkbookBeforeCloseEvent event (with XL being XL = new
Excel.Application(null, ExcelDnaUtil.Application) as to release all
proxy com objects in NetOffice (except for the Application object)
when a workbook closes. The 'false' ensures that we do not release our
event listeners.

Also, another thing that creates the errors (even with NetOffice) is
saving objects like Excel.Range into static variables for use
throughout the life of your application. This seems to prevent those
from being disposed properly, so you have to use other means of saving
Excel.Range information or other objects if you intend to save them
statically between macro executions.

Hope this helps. Thanks for the suggestion Govert.

On Jul 14, 12:45 am, Mad1523 <mad1...@gmail.com> wrote:
> Govert,
>

> You are right, this does look promising:http://netoffice.codeplex.com/wikipage?title=Tec_Documentation_Englis...