In Excel VSTO, how can I check if a worksheet belongs to a closed workbook?

Question

If I have a reference to Worksheet and I close it's parent Workbook, the reference doesn't go away. But I can't figure out how I should check to make sure these sheets don't exist. Checking for null doesn't work.

Example:

Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet sheet = (Worksheet)book.Worksheets[1]; // Get first worksheet
book.Close(); // Close the workbook
bool isNull = sheet == null; // false, worksheet is not null
string name = sheet.Name; // throws a COM Exception

This is the exception I get when I try to access the sheet:

System.Runtime.InteropServices.COMException was caught
  HResult=-2147221080
  Message=Exception from HRESULT: 0x800401A8
  Source=MyProject
  ErrorCode=-2147221080
  StackTrace:
       at Microsoft.Office.Interop.Excel._Worksheet.get_Name()
       at MyCode.test_Click(Object sender, RibbonControlEventArgs e) in c:\MyCode.cs:line 413
  InnerException:

This wouldn't even be an issue if I could check for a workbook delete event, but Excel doesn't provide one (which is really annoying).

Is there some convenient way to make sure I don't use these worksheets?

Andrew · Answer 1 · 2012-03-06T20:55:18.967

3

I use this method :

        private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

or you can try something like this:

    static bool IsOpened(string wbook) 
{ 
    bool isOpened = true; 
    Excel.Application exApp; 
    exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); 
    try 
    { 
        exApp.Workbooks.get_Item(wbook); 
    } 
    catch (Exception) 
    { 
        isOpened = false; 
    } 
    return isOpened; 
}

edited Mar 06 '12 at 20:55

answered Mar 06 '12 at 20:41

Andrew

7,095
13
55
109

How do I know when to call this though? I have some potentially open or potentially closed worksheet. If it's open I want to do something with it. If it's closed I don't. The problem is I have no way of knowing if it's open or closed. This method seems like it will help only if I know it's closed. – Kris Harper Mar 06 '12 at 20:47
I can do this, but I'd rather only use it as a last resort. I'm generally of the opinion that [exceptions shouldn't be used for flow control.](http://stackoverflow.com/questions/729379/why-not-use-exceptions-as-regular-flow-of-control) – Kris Harper Mar 06 '12 at 21:06
I agree, but I don't think you will use this method a lot, so it should not impact your application performance. In addition, I tried :) Hopefully you will be able to come up with better solution! – Andrew Mar 07 '12 at 02:43

Peter Majeed · Accepted Answer · 2012-03-15T17:08:19.750

If the other solutions fail, another way to handle this is to store the name of the workbook after it opens, then check to see if that name exists in the Workbooks collection before referencing the sheet. Referencing the workbooks by name will work since you can only have uniquely named workbooks in each instance of Excel.

public void Test()
{
    Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook;
    string wbkName = book.Name; //get and store the workbook name somewhere
    Worksheet sheet = (Worksheet)book.Worksheets[1]; // Get first worksheet
    book.Close(); // Close the workbook
    bool isNull = sheet == null; // false, worksheet is not null
    string name;

    if (WorkbookExists(wbkName))
    {
        name = sheet.Name; // will NOT throw a COM Exception
    }
}

private bool WorkbookExists(string name)
{
    foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks)
    {
        if (wbk.Name == name)
        {
            return true;
        }
    }

    return false;
}

Edit: for completeness, a helper extension method:

public static bool SheetExists(this Excel.Workbook wbk, string sheetName)
{
    for (int i = 1; i <= wbk.Worksheets.Count; i++)
    {
        if (((Excel.Worksheet)wbk.Worksheets[i]).Name == sheetName)
        {
            return true;
        }
    }

    return false;
}

This works, but only if I happen to have the `Workbook` at the time. My example code was simple, but in practice, I have several methods which only take a `Worksheet` object. Nevertheless, I'm going to accept your answer since I ended up doing something similar. Namely, I checked `Application.Workbooks.Count > 0`. Since I only ran into this issue when there were no workbooks open, this check sufficed for my needs. Thanks for your input though. — Kris Harper, Mar 08 '12 at 20:46

score 0 · Answer 3 · answered Mar 06 '12 at 20:32

0

I've not tried this, but you could check if the Workbook sheet.Parent exists in the Application.Workbooks collection.

answered Mar 06 '12 at 20:32

Joe

114,633
27
187
321

Won't work. Calling `sheet.Parent` causes the same exception as calling `sheet.Name`. Good thought though. – Kris Harper Mar 06 '12 at 20:43

In Excel VSTO, how can I check if a worksheet belongs to a closed workbook?

3 Answers3