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

Question

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.

Sam Hobbs · Answer

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));

Vulpes · Answer

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; }