Click here to Skip to main content
14,774,519 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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
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

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
workbook = excelApp.Workbooks.Add(1);

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

and add the following code for releasing your com references.
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.
   
Comments
CHill60 10-Feb-13 8:56am
   
+5
Jibesh 10-Feb-13 15:55pm
   
Thank You CHill.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900