Interop.Excel - COM object that has been separated from its underlying RCW

[Mr.Orange]

I'm trying to figure out the exact cause of an error I am getting and I was hoping someone could point me in the right direction.

The error I get is as follows:
Error # 5 was generated by Interop.Excel - COM object that has been separated from its underlying RCW can not be used.

The COM object I am using is the MS Excel 10.0 Object Library.

I create the following types of objects using this COM object:
Excel.Application
Excel.Workbook
Excel.Worksheet
Excel.ChartObjects
Excel.ChartObject
Excel.Chart
Excel.Range
Excel.Series

I use these objects to access, modify and manipulate data in an Excel spreadsheet.

i.e.

XL_App = CType(CreateObject("Excel.Application"), Excel.Application)

XL_Book = XL_App.Workbooks.Open(STORAGE_DIRECTORY & "Spreadsheets\" & XL_File_Name & ".xls")

XL_Sheet = CType(XL_Book.Worksheets(1), Excel.Worksheet)

and so on...

My problem seems to be related to the fact that I use these objects in threads that I create using System.Threading.Thread.

Sometimes I seem to be able to get away with using these objects in threads and sometime I don't. A specific instance I have found that seems to cause problems is when I use these objects in a thread that calls Application.DoEvents().

I have tried using GC.KeepAlive on all of the Excel objects.

I don't want to make this post too long, so I'm just going to leave it at that. I'm just hoping that someone has some general advice they could give me on this issue.

Thanks.

[Craig Gemmill]

I've only come across this error once, and it was when I released the instance of the COM object and then tried to access one of it's members. Remember that if you have more then one reference to the same object (which you usually do in an MTA), and you release/destroy that reference, you are also destroying the object it references.

Example:


Dim x as New Object
Dim y as Object

y = x

y = Nothing

'You just destroyed x and y (It doesn't really get destroyed until
'the GC gets to it, but ignore that for this example), so now if you
'try to access a member of x, or the reference to x (y), it will fail.


I suspect that is what is happening to you.

[Mr.Orange]

Thanks for the help Craig.

I took your advice and looked for objects that I was setting to Nothing.

There was a System.Windows.Forms.Form object in my code that I would creating only when I needed it using a Dim statement and then set to Nothing when I was finished with it, i.e.:

Public Class My_Form
Inherits System.Windows.Forms.Form
...
End Class

Dim My_Object As New My_Form
My_Object.Visible = True
...
My_Object.Visible = False
My_Object = Nothing

I'm not completely sure why this would cause the error I was getting, but making a single global My_Object that I simply make visible when I need it and invisible when I'm finished with it seems to have stopped the error from occurring (for now anyway).

So, here's my very uncomputerscientific conclusion:

Although I'm unclear on why, I now fairly certain that two things I should not do inside threads that uses MS Excel 10.0 Object Library objects (and perhaps other Object Library objects) are:

- make an Application.DoEvents call
- create a form using a Dim statement and set it to Nothing when I'm finished with it

[DSJ]

I haven't totally got my brain wrapped around this yet, but I'm pondering the implications of call DoEvents from a thread other than the main one. Doesn't seem right to me (ie, why would you need to???) and may be what is causing your problem.

[Mr.Orange]

I assumed (probably foolishly) that a DoEvents call within a thread would allow forms that were created in that thread to handle their events.

But I guess maybe the fact that DoEvents is part of the Application class should indicate that this isn't the case, (not that I'm saying for sure it isn't the case).

[DSJ]

I'm not sure either, but will let you know if I find any info on the topic. Another thing to keep in mind is that the COM objects are all build for STA threading, so that may also be part of your issue.

[Mr.Orange]

I was unaware of that. Perhaps that is the root of my problems. Thanks for the info.