c# winforms VS2017 linking with excel: error when closing form

Question

see image

This is considered a duplicate post as the exact codes are used for another question (about 5 years ago.).Basically, I get the error System.NullreferenceException: 'Object reference not set to an instance on an object'. for line xlWorkBook.Close(true, misValue, misValue);each time I closed the form (see image). Based on the original question: Inserting multiple textbox data into an Excel file, I cannot find others having the same problem as me. The code I've used is the same as the link:

`using System;
 using System.Collections.Generic;
 using System.ComponentModel;
 using System.Data;
 using System.Drawing;
 using System.Linq;
 using System.Text;
 using Excel = Microsoft.Office.Interop.Excel;
 using System.Windows.Forms;

 namespace Vehicledettry
   {
     public partial class Form1 : Form
      {
    Microsoft.Office.Interop.Excel.Application xlexcel;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        xlexcel = new Excel.Application();

        xlexcel.Visible = true;

        // Open a File
        xlWorkBook = xlexcel.Workbooks.Open(" C:\\vehicledet.xlsx", 0, true, 5, "", "", true,
        Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlWorkSheet.Cells[1, 1] = "Plate Number";
        xlWorkSheet.Cells[1, 2] = "Car Model";
        xlWorkSheet.Cells[1, 3] = "Car Brand";
        xlWorkSheet.Cells[1, 4] = "Mileage";
    }

    private void button2_Click(object sender, EventArgs e)
    {
        int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;

        xlWorkSheet.Cells[_lastRow, 1] = Plate Number.Text;
        xlWorkSheet.Cells[_lastRow, 2] = Car Model.Text;
        xlWorkSheet.Cells[_lastRow, 3] = Car Brand.Text;
        xlWorkSheet.Cells[_lastRow, 4] = Mileage.Text;

    }

    private void button3_Click(object sender, EventArgs e)
    {
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);

    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

}

I've tried copying xlWorkBook = xlexcel.Workbooks.Open(" C:\\vehicledet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); after every button event clicked but the same error kept occurring. Hope that I could get some help. Thanks.

Answer 1

Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel 
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \\Saving the file when changing
wb.Close(); 
exc.Quit();

Answer 2

(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;


namespace exceltry
  {
    public partial class Form1 : Form
     {
       public Form1()
        {
           InitializeComponent();
        }

private void button1_Click(object sender, System.EventArgs e)
  {
        Excel.Application xlexcel = null;
        Excel._Workbook xlWorkbook = null;
        Excel._Worksheet xlWorkSheet = null;
        Excel.Range oRng;

    try
       {
          //start excel and get application object
          oXL = new Microsoft.Office.Interop.Excel.Application();


          //open existing workbook
            oWB = oXL.Workbooks.Open("C:\\vehicledet.xlsx");
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

         //Add table headers going cell by cell.
          xlWorkSheet.Cells[1, 1] = "Plate Number";
          xlWorkSheet.Cells[1, 2] = "Car Model";
          xlWorkSheet.Cells[1, 3] = "Car Brand";
          xlWorkSheet.Cells[1, 4] = "Mileage";

         //Format A1:D1 as bold, vertical alignment = center.
          xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
          xlWorkSheet.get_Range("A1", "D1").VerticalAlignment = 
          Excel.XlVAlign.xlVAlignCenter;

          // insert text at every last row
            int _lastRow = xlWorkSheet.Range["A" +    xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;

            xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
            xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
            xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
            xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;


            //AutoFit columns A:D.
            oRng = xlWorkSheet.get_Range("A1", "D1");
            oRng.EntireColumn.AutoFit();

            //Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
            xlexcel.Visible = false;
            xlexcel.UserControl = true;

            //can save update to same file but wont close unless user press x button
            xlWorkbook.Save();
            MessageBox.Show("Data saved successfully");
            xlWorkbook.Close();

            //allow excel to auto close by itself 
            oXL.Quit();
            Marshal.ReleaseComObject(oXL);

        }
        catch (Exception theException)
        {
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat(errorMessage, theException.Message);
            errorMessage = String.Concat(errorMessage, " Line: ");
            errorMessage = String.Concat(errorMessage, theException.Source);

            MessageBox.Show(errorMessage, "Error");
        }
    }
}