Exception in Excel 2007 operation on Vista

Question

Hi,

I have developed an console application in C# .net which creates a new excel & performs some operations in it [Interop].

I have added Interop.Microsoft.Office.Interop.Excel.dll as reference in my project

The code works fine on XP & in Vista too. But if i keep my exe in one of the folder then it gives me excception

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C0 00-000000000046} failed due to the following error: 80090006.

Unhandled Exception: System.Exception: Unhandled exception during execution ---> System.NullReferenceException: Object reference not set to an instance of an object.

Anyone has any idea whether this could be possible because of stricter policies which might have been set on any folder. I am asking this because, if i copy this exe to another folder, the exe works perfectly.

The piece of code where execption is thrown is,

/* Create spreadsheet update data */
InteropExcel.Workbook oWorkbook = null;
InteropExcel.Worksheet oWorkSheet = null;
InteropExcel.ApplicationClass appExcel = null;

try
{
  // Probable at this line
  appExcel = new InteropExcel.ApplicationClass();

  oWorkbook = (InteropExcel.Workbook)appExcel.Workbooks.Add(true);
  oWorkSheet = (InteropExcel.Worksheet)oWorkbook.ActiveSheet;
  // ...

I am also not able to handle it even i have written this code in try-catch block.

Can anyone suggest any solution for it?

Thanks,

Amit

Answer 1

Hi Amit,

It is difficult to know for sure what could be causing this. So, first a few questions, which you can reply to or clarify by editing your original question, above.

(1) When you say that you have referenced "Interop.Microsoft.Office.Interop.Excel.dll", I assume that you mean Microsoft.Office.Interop.Excel.dll?

Also, if you check the properties for this reference does it show that it's location is in the GAC? You can check this by going into the Solution Explorer, expanding the Project, expanding the References, right clicking on the Microsoft.Office.Interop.Excel reference and then checking the Path property. If it is in the GAC, the Path property should begin with "C:\Windows\Assembly\GAC...".

(2) It also appears that you have a using statement that defines InteropExcel somewhere? Something like this:

using InteropExcel = Microsoft.Office.Interop.Excel;

Is this correct, or is the InteropExcel a separate reference?

(3) When you say that you are moving your exe, are you moving the exe only, or the entire folder that holds the exe and the associated files? A .NET assembly is not self contained, it needs the references, config files, and/or other files to be within the same folder as the exe, or sometimes within a subfolder of the same folder that holds the exe. Make sure that you move everything together.

(4) You should run your code in debug mode, hosted from within the Visual Studio IDE, so that you can know exactly which line is throwing an exception. My guess is that your first line is returning null:

appExcel = new InteropExcel.ApplicationClass();

And then I think your next line is throwing a NullReferenceException, because your 'appExcel' reference is null:

oWorkbook = (InteropExcel.Workbook)appExcel.Workbooks.Add(true);

(Hmmm... actually, the more I think about this, the less this makes sense. Not tested, but I would not expect that a call to appExcel = new InteropExcel.ApplicationClass() would quietly return null. I would expect it to either correctly return an application object or throw an exception, but not quietly fail by returning null. But maybe. If not, then I think your exception might be occurring in some code outside of the code you show. So you really need to run this within the Visual Studio IDE so that you can know which line is failing.)

(5) Additionally, your call to the Workbooks.Add method is not correct. The Template parameter is used to determine which workbook, if any, shall act as the template for the new workbook. In general, the argument passed in should be Type.Missing in order to omit this parameter; this will allow a new workbook to be created based on a default, blank workbook. If you do pass in an argument, it can either be a string holding a full path to the workbook you wish to act as the template for the new workbook, or an Excel.XlWBATemplate constant such as Excel.XlWBATemplate.xlWBATChart or Excel.XlWBATemplate.xlWBATWorksheet to determine the kind of workbook to be created. Again, in general, this parameter should be omitted by passing in Type.Missing. (For more on this, see help on the Workbooks.Add Method.)

Therefore, I suggest that you change your line from:

oWorkbook = (InteropExcel.Workbook)appExcel.Workbooks.Add(true);

to

oWorkbook = (InteropExcel.Workbook)appExcel.Workbooks.Add(Type.Missing);

(6) In general you should be making use of Application and not the ApplicationClass. I know that this seems odd because Excel.Application is an interface, and creating a new interface seems paradoxical, but this is how it should be done when automating MS Office programs. (For more on this, see Don't use ApplicationClass (unless you have to) and Excel interop: _Worksheet or Worksheet?.)

Therefore, your lines:

InteropExcel.ApplicationClass appExcel = null;
appExcel = new InteropExcel.ApplicationClass();

should instead be:

InteropExcel.Application appExcel = null;
appExcel = new InteropExcel.Application();

I don't know which of these issues is causing your problem, probably a few of them are in play here, but all of them should be checked and/or corrected. Hopefully one of these will correct your problem.

If none of these fixes it, you should post back with some more information on all of these items that I have highlighted, and/or include anything else you may have figured out in the mean time.

I've got my fingers crossed for you...

Mike

Answer 2

SpreadsheetGear for .NET is easier to use and deploy from .NET applications (including console applications) and much faster than Excel via COM Interop.

You can download a free trial here.

Disclaimer: I own SpreadsheetGear LLC