{“Unable to cast COM object

{“Unable to cast COM object

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:

  1. Mostly your workbook is corrupt
  2. Your workbook or sheet is password protected
  3. Given sheet does not exists in the workbook
  4. Given sheet is deep hidden

Leave a Reply

Your email address will not be published.