Why can't set cast an object from Excel interop?
Solution 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.
Solution 2
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
Solution 3
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.
Solution 4
Odd one. According to this page, it's supposed to be of type Sheets
not Worksheets
. Haven't tested - give it a whirl?
AngryHacker
Updated on July 18, 2022Comments
-
AngryHacker almost 2 years
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?
-
Aaronaught about 14 yearsThis question is tagged
.net-2.0
, which means he can't use Linq to XML or any of the packaging classes. Even if he could, this still wouldn't answer the question. -
AngryHacker about 14 years@Zach & @Joel are right too, but you were the first to the starting gate, the the green check.
-
TheBlastOne almost 13 yearsEven though this is quite well-documented, it might be one of the questions/answers that make so worthwhile.
-
Ross Brasseaux about 7 yearsThank you for this. The "gotcha" for me was that
Excel.Workbook.Worksheets
returns anExcel.Sheets
object—not anExcel.Worksheets
object.