Steve Chambers Steve Chambers - 3 months ago 10
C# Question

How to detect whether workbook is closed?

I'm working on a C# project that uses

Microsoft.Office.Interop.Excel.Application
to read values from an Excel file:

try {
Application xlApp = new Application();
Workbook workbook = xlApp.Workbooks.Open(filename)
// ... Load Excel values ...
} finally {
// ... Tidy up ...
}


In the
finally
block I'd like to make sure everything is closed and disposed of properly so nothing hangs around in memory and Excel closes cleanly. Have seen various threads about what this code should look like (more complex than I thought!) but one thing it might include is:

if (workbook != null) {
workbook.Close();
// ... possibly also Marshal.FinalReleaseComObject(workbook);
}


However, this throws an error if the workbook is already closed so how can I safely check this? Would prefer not to just catch the error if possible as this type of thing tends to distort debugging. Is there a clean way of finding out the workbook state before closing?

One more q - am wondering if
workbook.Close()
is needed if
xlApp.Quit();
is done afterwards - would quitting the Excel application cause
workbook.Close()
(and any COM object releasing) to happen implicitly?

Answer

As you are opening the workbook the best advice is to keep track of the workbook and close it when appropriate. If your code is quite detailed then you could store a Boolean value indicating whether the file is currently open or closed.

In Excel there is no property such as IsOpen. You could try to reference the workbook:

Workbook wbTest = xlApp.Workbooks.get_Item("some.xlsx");

but this creates a COM error if the book is not open, so gets quite messy.

Instead, create your own function IsOpen that returns a boolean and loops through the currently open workbooks (the Workbooks collection) checking the name, using code like this:

foreach (_Workbook wb in xlApp.Workbooks)
{
    Console.WriteLine(wb.Name);
}

workbook.Close() would not be necessary if the workbook has been saved - reflecting the normal behaviour of Excel. However, all Excel object references need to be released. As you have discovered, this is a little fiddly, and Close and Quit do not achieve this on their own.


    static bool IsOpen(string book)
    {
        foreach (_Workbook wb in xlApp.Workbooks)
        {
            if (wb.Name.Contains(book))
            {
                return true;
            }
        }
        return false;
    }