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

Have you seen this [post](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects)? — Automate This, Oct 01 '13 at 20:09

score 4 · Accepted Answer · answered Oct 01 '13 at 20:08

4

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

answered Oct 01 '13 at 20:08

Khan

17,904
5
47
59

I tryed what you wrote and there is no error but my app still won’t write and save to excel. Could you help me? – Krkec Oct 02 '13 at 10:12
1

If my answer has solved your question, please upvote and select it as the answer. As for writing to excel, your question above has nothing to do with writing or saving. Please open another question stating your issue. – Khan Oct 02 '13 at 12:41

Open and save excel document in c#

1 Answers1