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?

Automate This
– Automate This

2013-10-01 20:09:28 +00:00
Commented Oct 1, 2013 at 20:09 — Automate This
– Automate This, Commented Oct 1, 2013 at 20:09

Khan · Accepted Answer · 2013-10-01 20:08:39Z

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 1, 2013 at 20:08

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Krkec Over a year ago

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?

Khan Over a year ago

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.

Collectives™ on Stack Overflow

Open and save excel document in c#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related