Why can't set cast an object from Excel interop?

Question

Trying to get a reference to the worksheets (using Excel interop):

Excel.Application xl = new Excel.ApplicationClass();
Excel.Workbooks xlWorkBooks = xl.Workbooks;
Excel.Workbook xlWorkBook = xlWorkBooks.Open(fileName, 0, false, 5, "", 
                      "", true, Excel.XlPlatform.xlWindows, "\t",
                      false, false, 0, true, 1, 0);

// Next line crashes
Excel.Worksheets xlWorkSheets = (Excel.Worksheets) xlWorkBook.Worksheets; 

The error is that it cannot cast it:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208B1-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Is my cast incorrect?

Answer 1

Yes, your cast is wrong.

_Workbook.Sheets gives you a Sheets instance. This interface gives you all types of sheets, not just worksheets; mainly, it includes charts, macro sheets, etc.

On the other hand, the Worksheets interface only gives you worksheets - not charts.

The interfaces are not assignable to each other; therefore, you get the COM error. It's confusing - I'm not even sure if it's possible to get an instance of the Worksheets interface through the PIA - but that's Office Interop for ya.

As long as you use the _Workbook.Worksheets property instead of the _Workbook.Sheets property, you should get an instance of Sheets that only returns Worksheet objects - in spite of the fact that the interface is capable of providing other types of sheets.