Microsoft Interop Excel closing running instances of excel?

Question

Hey everyone, I've been reading about a lot of people finding it hard to close instances of a running excel application created using Com objects. I've managed to sporadically cause Excel instances to be completely closed however not all the time (I'll get to this later).

---

My problem is rather more complex than simply closing a running instance of Excel as I am trying to create an app that will:

1. Create an Excel file using one of many available templates (this works fine)

2. Populate cells of the template depending on criteria selected in my app (also works fine)

3. Allows users to continue entering values which cannot be derived from data within my app (here's what makes closing the processes and removing com objects more tricky)

4. Save the data upon closing Excel (I can get this working without cleaning up the com object references however this obviously means the Excel process hasn't been terminated, tried cleaning up the com object references but then it wont allow me to save the worksheet AFTER the user closes excel, or not how I've implemented it anyway).

5. Ideally I would like to terminate the Excel process when the user closes Excel. (Here's where the problem lies)

---

As for the the com object cleanup, I have a cleanup method such as follows:

private void cleanUp()
{
    for (int i = 0; i < ranges.Count; i++)
    {
        ranges[i] = null;
    }

    for(int i = 0; i < worksheets.Count; i++)
    {
        worksheets[i] = null;
    }

    for (int i = 0; i < worksheetwrappers.Count; i++)
    {
        worksheetwrappers[i] = null;
    }
    for (int i = 0; i < workbooks.Count; i++)
    {
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks[i]) > 0)
                {
            workbooks[i].Close(false, "", false);
                        workbooks[i] = null;
                }
    }
    for (int i = 0; i < instances.Count; i++)
    {
                while (System.Runtime.InteropServices.Marshal.ReleaseComObject(instances[i]) > 0)
                {
                    instances[i].Quit();
                        instances[i] = null;
                }
        }
} 

I have yet to refactor this and make it nice and tidy so unfortunately you're getting the very basics of what I have atm.

---

Basically my problem is knowing when to dispose of all the com objects and when to perform cleanup as the user is required to enter more data after the spreadsheet has been populated, meaning i cant automatically perform a clean-up after the spreadsheet has been populated.

I'm wondering if i'll have to use an observer of some sort to listen for when excel closes and then dispose of all Com objects (which is a pain) or if someone has some clever idea I haven't thought of.

Thanks for any help,

Nic

Answer 1

You could try to work around your problem.

1: Create worksheet based on template and populate data

2: Start process Excel with the parameter "newfilename"

3: Wait for process to end

4: Open worksheet, read data and dispose of the objects

Perhaps this is an option - you'll at least know when the user has ended it's excel session.