Open and save excel document in c#

Question

I am trying to open and save an Excel document in C# but I always get error: "COM object that has been separated from its underlying RCW cannot be used." at line ExcelApp.Quit(); I googled lots of solutions but none works for me so far.

Here's my code:

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

namespace test_excel
{
    public partial class Form1 : Form
    {

        Excel.Application ExcelApp = null;
        Excel.Workbook ExcelWorkBook = null;
        Excel.Sheets ExcelSheets = null;

        Excel.Worksheet MySheet = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExcelApp = new Excel.Application();
            ExcelApp.Visible = false;
            ExcelWorkBook = ExcelApp.Workbooks.Open("c:\\test.xls", 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            ExcelSheets = ExcelWorkBook.Worksheets;
            MySheet = (Excel.Worksheet)ExcelSheets.get_Item("Sheet1");

            int a = (int)MySheet.Range["a1"].Value2;
            label1.Visible = true;
            int b = a + 1;

            label1.Text = a.ToString() + " | " + b.ToString();
            MySheet.Range["a1"].Value2 = b;



        }
        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();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelSheets);


                ExcelWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                releaseObject(ExcelApp);
                releaseObject(ExcelSheets);
                releaseObject(ExcelWorkBook);
                ExcelApp.Visible = false;
                ExcelApp.UserControl = false;

                ExcelWorkBook.SaveAs("c:\\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
                             null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
                             false, false, null, null, null);
                ExcelWorkBook.Close();
            }
            catch (Exception err)
            {
                String msg;
                msg = "Error: ";
                msg = String.Concat(msg, err.Message);
                msg = String.Concat(msg, " Line: ");
                msg = String.Concat(msg, err.Source);
                Console.WriteLine(msg);
            }
            finally
            {

                try
                {


                    ExcelApp.Visible = false;
                    ExcelApp.UserControl = false;
                    ExcelWorkBook.Close(true, null, null);
                    ExcelApp.Workbooks.Close();
                }
                catch { }


                ExcelApp.Quit();


                if (MySheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(MySheet); }
                if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
                if (ExcelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); }


                MySheet = null;
                ExcelWorkBook = null;
                ExcelApp = null;
                GC.Collect();
            }
        } 
    }
}

Answer 1

I think you're causing ExcelApp to get GC'd here: releaseObject(ExcelApp);

Then you're trying to tell it to perform a method here: ExcelApp.Quit();

Try moving releaseObject(ExcelApp); to after you call Quit