Unable to cast COM object of type 'Excel.WorksheetClass' to interface type 'Excel._Worksheet'. This ...

Gomathi Palaniswamy

objExcel = new Excel.Application();
            Excel.Worksheet tmpWorksheet = new Excel.Worksheet();
            objExcel.Visible = true;
            objWorkBook = objExcel.Workbooks.Open(strExcelFile, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,false,false);
            string[] strSheetName = new string[objWorkBook.Worksheets.Count-1];
            int i = 0;
            foreach (Excel.Worksheet tmpWorkSheet in objWorkBook.Worksheets)
            {
                strSheetName[i] = tmpWorksheet.Name;//error occurs here
                i += 1;
            }
            objExcel.Quit();

the above coding sholwing the error below error

"Unable to cast COM object of type 'Excel.WorksheetClass' to interface type 'Excel._Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID "

help me pls.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
I think part of the problem is that objWorkBook.Worksheets.Count will not be large enough if there are sheets that are not used. So in other words your workboook might show sheet1 through sheet6 at the bottom but only sheet1 and sheet2 have data. If so then objWorkBook.Worksheets.Count will be two but when you use "Excel.Worksheet tmpWorkSheet in objWorkBook.Worksheets" you will get 6 worksheets. I assume there is a way to get it to work so you should look at the articles in this web site for a better answer and if you do not get an answer that way then look in the MSDN and other resources. The following works for me but I assume it is not the best way.
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("152714.xls");
string[] strSheetName = new string[xlWorkBook.Worksheets.Count - 1];
int i = 0;
foreach (Excel.Worksheet xlWorkSheet in xlWorkBook.Worksheets)
{
if (xlWorkSheet.UsedRange.Cells.Count > 1)
{
strSheetName[i] = xlWorkSheet.Name;
i += 1;
}
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Console.WriteLine(string.Join(" ", strSheetName));
Vulpes14y ago
Try removing this line, which doesn't appear to be doing anything:

Excel.Worksheet tmpWorksheet = new Excel.Worksheet();

and changing the foreach statement to:

foreach (Excel._Worksheet tmpWorkSheet in objWorkBook.Worksheets)
{
strSheetName[i] = tmpWorksheet.Name;
i += 1;
}