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

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

Excel.Application xl = new Excel.ApplicationClass();
Excel.Workbooks xlWorkBooks = xl.Workbooks;
Excel.Wor开发者_运维问答kbook 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?

---

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.

---

According to MSDN, Workbook.Worksheets returns Microsoft.Office.Interop.Excel.Sheets.

So you'd cast it like this:

Microsoft.Office.Interop.Excel.Sheets sheets = 
    (Microsoft.Office.Interop.Excel.Sheets)xlWorkBook.Worksheets

Or assuming Excel maps to Microsoft.Office.Interop.Excel (as appears from your question)

Excel.Sheets sheets = (Excel.Sheets)xlWorkBook.Worksheets

---

If it works on one environment but not another, check the HK Classes Root/TypeLib reg keys.

It is possible that you're trying to run for HKCR\TypeLib{00020813-0000-0000-C000-000000000046}\1.6 but something the user installed has added the key: HKCR\TypeLib{00020813-0000-0000-C000-000000000046}\1.7 causing the Interop call to throw an exception.

Or if that isn't it, it could be something in the GAC because of different OS versions.

I had this issue where it worked on our developer machines running Windows 7, and caused this error on a user's machine running XP.

---

Odd one. According to this page, it's supposed to be of type Sheets not Worksheets. Haven't tested - give it a whirl?

---

If you're dealing with Excel 2007+ I'd suggest using System.IO.Packaging + System.Xml.Linq (LINQ to XML) to manipulate excel sheets. It's much cleaner and dosen't require excel to actually be installed on the machine you're running your app on.

You'll also run into less COM collisions (such as above in your post).

If you're trying to edit Excel 2003 or earlier, then unfortunately I'm unable to help you.

---

This might have happened after and upgrade to latest Office version. Try going to Programs and Features, finding the installation and 'repairing' it. Apparently, it updated and/or re-registers the interop dll used to create those objects.

This might need to be done in combination with the registry fix described by @codesforcoffee.