I keep getting the "Object reference not set to an instance of an object." error when I run my project.

Question

_{Monday, March 17, 2008 6:38 PM}

Here's the code:

Code Snippet

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using office = Microsoft.Office.Core;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }
        private Excel.Application ExcelApp = null;
        private void Form1_Load(object sender, EventArgs e)
        {
            ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Visible = true;
        }
     
        private void button1_Click(object sender, EventArgs e)
        {
           //Open a workbook in C:\ named test.xlsx
            // the full path name of that Excel file in the first parameter.
            ExcelApp.Workbooks.Open(@"C\Test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //Read cell A1's value and use Messagebox to pop it up
            //If you want to get multiple cells' range, like from A1 to A10
            //Use ExcelApp.get_Range("A1:A10",Type.Missing)
            Excel.Range range = ExcelApp.get_Range("A1", Type.Missing);
            MessageBox.Show(range.Value2.ToString());

        }

        private void button3_Click(object sender, EventArgs e)
        {
            //Write to cell A1
            Excel.Range range = ExcelApp.get_Range("A1", Type.Missing);
            range.Value2 = "Test String put in Cell A1";
        }
       

        private void button4_Click(object sender, EventArgs e)
        {
            //How to create a new blank workbook
            ExcelApp.Workbooks.Add(Type.Missing);
            //How to create a new blank worksheet in current workbook
            ExcelApp.Worksheets.Add(Type.Missing,Type.Missing,1,Type.Missing);

        }

        private void button5_Click(object sender, EventArgs e)
        {
            //Excel workbook's name is, by design, not to be modified unless
            // you save it.  You can use SaveAs() method to save it as
            //another named file.
            ExcelApp.ActiveWorkbook.SaveAs(@"C:\Modified Name of Workbook.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //To modify a sheets name you can do like this:
            Excel.Worksheet worksheet = ExcelApp.ActiveSheet as Excel.Worksheet;
            worksheet.Name = "Modified Name of Worksheet";

        }
    }
}

The error is referring to the line -

Code Snippet

 ExcelApp.Workbooks.Open(@"C\Test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

as it is highlighted when the error comes up.  I have no idea what is going on.  I am a novice, but everything seems to check out here.  Does anyone have any ideas????

All replies (20)

_{Monday, March 17, 2008 7:18 PM ✅Answered}

I never like declaring variables like that.  I always get null reference errors sooner or later.

private Excel.Application ExcelApp = new Excel.ApplicationClass();

Instead of setting it to be nullnear the top of your code.

Rudedog

_{Wednesday, March 19, 2008 4:57 AM ✅Answered}

Don't forget to set the visible to be true,after you opening the workbook.

ExcelApp.Workbooks.Open(@"C:\Test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

ExcelApp.Visible = true;

Thanks.

_{Monday, March 17, 2008 6:52 PM}

Assuming that the syntax of the Excel.Open() is correct, I suggest that you look at the path you are passing in:

ExcelApp.Workbooks.Open(@"C\Test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

or

ExcelApp.Workbooks.Open(@"C:\Test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

It is possible that the error is being misreported to you...

_{Monday, March 17, 2008 6:54 PM}

It's hard to tell from your code what the problem is, it looks like the Workbooks property is null; but you'd have to check it in the debugger.

See http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.officedev&lang=en&cr=US for questions and discussions relating to automating Office applications like Excel in .NET.

_{Monday, March 17, 2008 6:56 PM}

Shouldn't the path be @"C:\Test.xls" instead of @"C\\Test.xls" ? Or even @"C:\Test.xlsx" ?

_{Monday, March 17, 2008 7:10 PM}

Yes, it should.  I changed it, but get the same error.

_{Monday, March 17, 2008 7:11 PM}

ExcelApp.Workbooks.Open

put a stop on this line, before it executes and blows up, take a quick look at ExcelApp then ExcelApp.Workbooks, one of them should be null.

_{Monday, March 17, 2008 7:12 PM}

When I step through it, ExcelApp is null at the point it gets to the line where the error is referrenced.  What does that mean?  What is the fix?

_{Monday, March 17, 2008 7:20 PM}

Is the Form1_Load() method being run?

It looks like the private variable is never set. Please set a breakpoint in Form1_Load() to see if it being hit...

_{Monday, March 17, 2008 7:56 PM}

It's being set within the scope of Form_Load.  Outside of that method, it is still null.  If he made it into a private property, then the situation would be different.

_{Monday, March 17, 2008 8:01 PM}

That's impossible. If it is being set in Form1_Load(), it cannot become null all of a sudden. The only way this private variable can be null is if Form1_Load() is not called.
A constructor call can never return null, and a private variable cannot change by itself.

_{Monday, March 17, 2008 8:02 PM}

Why would changing it from a private field to a private property have any such effect?

_{Monday, March 17, 2008 8:16 PM}

Don't know why, but I've corrected null reference errors by making them into properties and referencing the property instead of the private field.  I just wrote a short test program to test that out and it made no difference.  I'll dig some more tonight.

However, I've run into situations where I set a variable to null, then intialize it an instance method, only to have other methods throw null exceptions when they try to reference the private field.  The problem goes away when I reference the private field through a property, though.

Rudedog

_{Monday, March 17, 2008 9:01 PM}

if it's set during form_load, why would it become null again?

_{Monday, March 17, 2008 9:12 PM}

I tried this and it seems to work well, but does not make the Excel workbook visible, which seems odd.  Any ideas

_{Monday, March 17, 2008 9:47 PM}

Good question.  I've also fixed similar problems by add a "this" reference to everything that could use it. 

I've been watching this behavior for a while, trying to make sense out of it.  It usually occurs when you simply declare a variable name, not by assigning a null which reserves memory.  I do not recall it ever ocurring when the variables are assigned values during class instantiation. 

It usually occurs to variables that are declared without values, and later assigned a value in another method.  It's almost as if the scope of the definition is valid for just that method.  But, when I access the private member through a property and/or a this reference, it's clean.

Rudedog

_{Monday, March 17, 2008 9:58 PM}

I'm sorry but this is impossible. If using "this." makes a difference, it means you have a local variable or parameter that has the same name.

The behavior you're describing can only occur if you use local variables that have the same name as an member defined in your class.

Although Microsoft recommends using camelCase without a prefix for private members, I strongly believe this is a very dangerous thing to do. It's very easy to mix up your locals with your members. I believe this is what was happening when you experienced the weird behavior you described. An interesting fact is that most of the .NET code written by Microsoft uses an underscore prefix for private members, although their coding guidelines tell you not to do that. I use underscores... makes code a lot cleaner and safer.

_{Monday, March 17, 2008 10:16 PM}

Okay.  I'll look at that.  I name public properties and their private members like this.

_ClassMember  -- private variable

ClassMember  -- public property

I do it that way to distinguish betweeen private members that are used for properties, and those that are not.  The strangest part is that I've been thinking about making a comment post titled, "This makes a difference."

_{Wednesday, April 16, 2008 6:53 PM}

For you path, try adding an extra backslash "C:\Test.xls",

_{Thursday, April 24, 2008 1:35 AM}

the path you first specified is correct but try to add backslash after each blackslashes..

sample: use @"C:\\test.xls" instead of @"C:\test.xls"

   use @"C:\\New Folder\\test.xls" instead of @"C:\New Folder\test.xls"