COM Failure:
This error is very unusual and annoying as it is hard to trap. Lets take a scenario with example fix. I have a very simple for each loop which iterates each sheet name in active workbook.
foreach (Microsoft.Office.Interop.Excel.Worksheet oSheet in Globals.ThisAddIn.Application.ActiveWorkbook.Sheets)
{
Console.Write(oSheet.Name);
}
Consider the active workbook has 10 sheets and my loop runs exactly 10 times but it again goes over loop for 11th time to validate condition and throws an exception. I broken my head for hours to understand that I got all 10 sheets name which I intended to but still goes under catch block.
Root Cause:
I don’t see exact root cause as various blogs says different causes. However, based on my scenario I could simply say that the file I was using was created using Office 2003 having extension .xls and having a macros within. Conclusively It was having some corrupt contents (bad names). I see a simplest solution to handle it.
Solution:
foreach (object oSheet in Globals.ThisAddIn.Application.ActiveWorkbook.Sheets)
{
//Casting oSheet into Worksheet object
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheet;
//Check if we have a valid sheet
if (xlSheet != null)
{
Console.Write(xlSheet.Name);
}
}
This is just not end of the world. As there are lots of bugs which were just unexpected in the same file. And I left with no choice but handling them through Try Catch. Sometimes you may encounter that you are not able to delete/select a sheet using code. I see following possible reasons:
- Mostly your workbook is corrupt
- Your workbook or sheet is password protected
- Given sheet does not exists in the workbook
- Given sheet is deep hidden
