Closing excel workbook from C#

Question

4.00/5 (1 vote)

See more:

Hi

I am trying to use Excel object to create a workbook. This works file unless the program crashes for some reason and next the workbook keeps open in the background. Next time when the code is run, it throws an error that workbook is already open. How do I close it ? I donot want to kill EXCEL process as that would close all other open workbooks.

Here is my code :

using System;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
    class Program
    {
        private static Excel.Application excelApp = null;
        private static Excel.Workbook workbook = null;
        private static Excel.Sheets sheets = null;
        private static Excel.Worksheet newSheet = null;

        static void Main(string[] args)
        {
            excelApp = StartExcel();
            //excelApp.Visible = true;
             
            workbook = excelApp.Workbooks.Add(1);                
            
            string filePath = "C:\\test.xls";
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            excelApp.DisplayAlerts = false;

            workbook = excelApp.Workbooks.Add(1);
            workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            
            workbook = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            sheets = workbook.Sheets;
            newSheet = (Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
            newSheet.Name = "My sheet";

            //excelApp.Application.ActiveWorkbook.Close(false, Type.Missing, Type.Missing); 
            workbook.Close(false, Type.Missing, Type.Missing);
        }

        public static Excel.Application StartExcel()
        {
            Excel.Application instance = null;
            try
            {
                instance = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                instance = new Excel.Application();
            }

            return instance;
        }
    }
}

Thanks,
Monica

Posted 9-Feb-13 15:20pm

Monica Agrawal

Add a Solution

Comments

CHill60 9-Feb-13 21:42pm

Try wrapping the code with a Try...Catch...Finally ... in the Finally section put the code to close and quit the excel app

Monica Agrawal 9-Feb-13 21:43pm

But if the application crashes in between and the code doesnt reach catch. Then how do we handle such situation next time code starts

CHill60 9-Feb-13 21:46pm

That's the whole point of the try-catch construction - catch the exception ("crash") and allow for the resources to be released afterwards ... see http://msdn.microsoft.com/en-us/library/dszsf989(v=vs.71).aspx

Monica Agrawal 9-Feb-13 21:55pm

Ofcourse. But even after adding try-catch the problem persists. If the program crashes in between, then it doesnt reach the catch block also and the resources do not get released. What I am trying to put is if there is a way to release the resources in case they are engaged at the start of the application itself

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jibesh · Accepted Answer · 2013-02-09T18:54:00

you havent released the com reference used by the Excel. The following link explains it well how to release the com objects.

how-to-properly-clean-up-excel-interop-objects[^]

From the above reference replace this line

C#

workbook = excelApp.Workbooks.Add(1);

to

C#

Excel.Workbooks workbooks = excelApp.Workbooks;
workbook = workbooks.Add(1);

and add the following code for releasing your com references.

C#

workbook.Close(false, Type.Missing, Type.Missing);

excelApp.Quit();
GC.Collect();

Marshal.FinalReleaseComObject(sheets);
Marshal.FinalReleaseComObject(newSheet);

Marshal.FinalReleaseComObject(workbook);
Marshal.FinalReleaseComObject(workbooks);

Marshal.FinalReleaseComObject(excelApp);

I dont understand why you call the Open statement just after the SaveAs, why dont you use the same workbook just saved before? you can remove that line.