use excel COM object in different thread

Question

I have been reading a lot of stuff and I think I understand whats going on but I don't know how to fix it.

I have a c# wpf app. the user clicks a button and I start up another thread to do a lot of work. part of that is to open excel, create an excel file and do a lot of things to the excel sheet to set it up.

after that part is done the user will then use the app and there are some button to further interact with the excel file, like adding in more content.

My problem is I'm losing that COM object to the excel file when the thread completes. not sure how to fix this. here is my code (in late binding to excel)

..I have a public static class to contain on excel object

public static class MyGlobals
{
    public static string ExcelprogID = "Excel.Application";
    public static dynamic xlWorkBook;
    public static dynamic xlApp;  
}

..then the use clicks a button and I kick off another thread

private void btnSearch_Click(object sender, RoutedEventArgs e)
{
    string dialogValues = "...";//replaced code for simplicity
    Thread t = new Thread(() => workerthread(dialogValues));
    t.SetApartmentState(ApartmentState.STA);
    t.Start();
}

..then in the new thread I do a lot but one of the things is creating the excel object and setting that excel sheet up.

private void workerthread(string e)
{
    MyGlobals.xlApp = Activator.CreateInstance(Type.GetTypeFromProgID(MyGlobals.ExcelprogID));
    MyGlobals.xlWorkBook = MyGlobals.xlApp.Workbooks.Add(MyGlobals.misValue);
    MyGlobals.xlApp.Visible = true; 
    //more code here to do stuff with excel.
}

...here is where my problem happens. now back on the main UI thread the user can click buttons to manipulate the stuff in the excel file. but I get this error

private void btnPlaceBM_Click(object sender, RoutedEventArgs e)
{
    //Excel.Worksheet BM;
    dynamic BM;
    try
    {
        BM = MyGlobals.xlWorkBook.Sheets["CADD Basemaps"];
    }
    catch
    {
        MessageBox.Show("Project Index Excel file was closed! Can not place any data to excel file!");
        return;
    }
}

here is the error I get

System.Runtime.InteropServices.InvalidComObjectException was caught: "COM object that has been separated from its underlying RCW cannot be used."

Could someone give me some help..can I reget that excel object somehow??

Office COM objects aren’t thread safe. Whilst your .exe can create a Office COM object in a worker thread, you can’t share it with other threads. — , Jul 13 '18 at 16:19
yes I believe the worker thread has terminated the buttons to place additional content are not enabled until the the excel file is finished setting up..which is until the worker thread is done — JED, Jul 13 '18 at 16:26
That's why if fails. If the thread that has created the object dies, then the associated apartment (STA) also dies, and the object reference is lost for everyone, including other threads. You want to keep the worker thread alive until the app closes of you really don't need excel anymore. @MickyD - this is not true, COM allows cross apartment communication, of course. — Simon Mourier, Jul 13 '18 at 16:36
Threading is not a minor detail in COM, the object itself is aware on which thread it was created. When the thread terminates then the object is dead as well. Cold hard fact is that you need to keep the thread running as long as the object(s) can be used *and* the RCWs are not yet finalized. The latter clause is a pretty ugly detail of course. Consider code [similar to this](https://stackoverflow.com/a/21684059/17034). — Hans Passant, Jul 13 '18 at 16:40
Maybe it is possible to create the COM object in the main thread. Than store the object in the GIT. Retrieve the pointer from the GIT whenever you need it. — xMRi, Jul 13 '18 at 17:17
I know I lose the object because the thread ends. but I don't know how to better handle it..i don't want to bog down the UI thread and make it unresponsive. is there any way to go and get that excel file COM object on the main thread? can I store some kind of pointer to it or something?? or how do I keep the thread from terminating — JED, Jul 13 '18 at 18:04
Even if it seems that you can get the Excel interop code to seem to run well on another thread, it will eventually come back to bite you. Excel expects to be running on the main STA thread. Even though this isn't directly applicable, parts of it are: https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office — Flydog57, Jul 13 '18 at 20:29

score 0 · Answer 1 · answered Jul 13 '18 at 18:30

okay from the comments it got me thinking. I was trying to see how to go and get the COM object from my worker thread before the thread ended. it would be too hard to change the code so the UI thread makes the COM objects before I kick off the worker thread because I cant create the COM objects until midway into the worker thread. and then it hit me. When I get to the part of the worker thread that creates the COM objects ill just dispatch it to the UI thread. now this may not be the best solution but it works. here is a code snippet of how to do that in the worker thread.

private void workerthread(string e)
{
    //code removed for simplicity..

    //Create COM object for excel app & excel workbook in Main thread to prevent object reference loss
    App.Current.Dispatcher.Invoke((Action)delegate
    {
        MyGlobals.xlApp = Activator.CreateInstance(Type.GetTypeFromProgID(MyGlobals.ExcelprogID));
        MyGlobals.xlWorkBook = MyGlobals.xlApp.Workbooks.Add(MyGlobals.misValue);
    });
    MyGlobals.xlApp.Visible = true; 
    //more code here to do stuff with excel.
}

use excel COM object in different thread

1 Answers1