How do I properly release an Excel file?

Question

_{Tuesday, April 28, 2015 12:58 PM}

Given inside a function

    Excel.Application xlApp = null;
    Excel.Workbook xlWorkbook = null;
    Excel.Sheets xlWorksheet = null;
    xlApp = new Microsoft.Office.Interop.Excel.Application();
    try
    {
      xlWorkbook = xlApp.Workbooks.Open(...);
      xlWorksheet = xlWorkbook.Worksheets;
      ...
      xlReleaseObject(xlWorksheet);
      xlReleaseObject(xlWorkbook);
      xlReleaseObject(xlApp);
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
      xlReleaseObject(xlWorksheet);
      xlReleaseObject(xlWorkbook);
      xlReleaseObject(xlApp);
    }

where

    static public void xlReleaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

For some reason, when the first function goes to "catch", it locks the Excel file ... read only.  How do I properly release an Excel file?

bhs67

All replies (11)

_{Wednesday, April 29, 2015 8:25 AM ✅Answered | 1 vote}

Hi,

Please don't use two or more dots in each line of the code, because when you deal with COM objects with two dots, for example:

Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(...);

It actually creates two RCW objects, one is for xlApp.Workbooks, another is for xlWorkbook. So you need to release all these two COM objects.

And please close or save the workbook then call the Quit method of Excel.Application to let the Excel application properly close itself. We don't recommend that you use Marshal.ReleaseComObjects method to release the Excel com objects. The right way is to let the Excel process to process the closing.

Take a look at this article for reference:

How to properly release Excel COM objects

<This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.>

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Tuesday, April 28, 2015 2:21 PM}

>> How do I properly release an Excel file?

Call the System.Runtime.InteropServices.Marshal.ReleaseComObject method for all your COM objects. Please refer to the following link for more information: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects

Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);

Please remember to close your threads by marking all helpful posts as answer and then start a new thread if you have a new question.

_{Tuesday, April 28, 2015 2:58 PM}

Call the System.Runtime.InteropServices.Marshal.ReleaseComObject method for all your COM objects. Please refer to the following link for more information: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects

Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);

I replaced the xlReleaseObject(...) with System.Runtime.InteropServices.Marshal.ReleaseComObject(...) -> does not fix the problem -> Excel is still locked.  It appears that catch() is having an impact.

bhs67

_{Tuesday, April 28, 2015 3:15 PM}

You need maintain a named reference for absolutely every COM object you access so you cannot do like this:

xlWorkbook = xlApp.Workbooks.Open(...);

Instead you should do like this:

Worksheets sheets = xlApp.Worksheets;
Worksheet sheet = sheets.Open(...);

And then call the ReleaseComObject method for each and every one of the objects.

Please read the answers on the link I provided for further information: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects.
"Never use 2 dots with com objects."

There is not much more you can do.

Please remember to close the thread by marking all helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

_{Tuesday, April 28, 2015 3:41 PM}

You need maintain a named reference for absolutely every COM object you access so you cannot do like this:

xlWorkbook = xlApp.Workbooks.Open(...);

Instead you should do like this:

Worksheets sheets = xlApp.Worksheets;
Worksheet sheet = sheets.Open(...);

And then call the ReleaseComObject method for each and every one of the objects.

Not sure I understand correctly.  I think this is the suggestion:

    Excel.Application xlApp =  new Microsoft.Office.Interop.Excel.Application();
    try
    {
      Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(...);
      Excel.Sheets xlWorksheet = xlWorkbook.Worksheets;
      ...
      Marshal.ReleaseComObject(xlWorksheet);
      Marshal.ReleaseComObject(xlWorkbook);
      Marshal.ReleaseComObject(xlApp);
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
      Marshal.ReleaseComObject(xlWorksheet);
      Marshal.ReleaseComObject(xlWorkbook);
      Marshal.ReleaseComObject(xlApp);
    }

which won't work for the catch -> the xlWorksheet and xlWorkbook are defined inside the try.

bhs67

_{Tuesday, April 28, 2015 4:34 PM}

Always use the finally block to release you objects, thats what its meant for. See the below example notice for the workbook sometimes we save with changes sometime on error we have to ignore:

Excel.Application xlApp=null;
            Excel.Workbook xlWorkBook=null;
            Excel.Worksheet xlWorkSheet=null;
            try {
               
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;

               

                xlWorkBook.SaveAs(@"C:\Users\Fouad\Desktop\test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
                Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                
            }
            catch (Exception ex)
            {
                xlWorkBook.Close(SaveChanges: false);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                xlApp.Quit();
                releaseObject(xlApp);
            }
private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
//No need for GC.Collect here anymore
            }
        }

Fouad Roumieh

_{Wednesday, April 29, 2015 12:01 PM}

Using three dots is standard syntax which means the text to include is lengthy which means there are many words.

Using the previous sentence as an example: Using three dots is standard syntax ... means there are many words.

In the first post I used xlWorkbook = xlApp.Workbooks.Open(...) instead of xlWorkbook = xlApp.Workbooks.Open(stWorkbookPathFilename, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false).

Using xlApp.Quit() works!  I removed all Releases and added xlApp.Quit() before exiting the function.  A simple solution!!!

bhs67

_{Wednesday, April 29, 2015 12:10 PM}

If you find your solution in multiple posts, note that you're allowed to mark many as answers. Not sure which solved your issue, and xlApp.Quit() exists in different post than the one you marked as answer.

Fouad Roumieh

_{Wednesday, April 29, 2015 12:42 PM}

If you find your solution in multiple posts, note that you're allowed to mark many as answers. Not sure which solved your issue, and xlApp.Quit() exists in different post than the one you marked as answer.

Fouad Roumieh

I was in the process of editing my previous post ... did not see your post until now.  I missed the Quit in your previous post ... was buried in a bunch of other text. 

Here is the end result that works:

    Excel.Application xlApp =  new Microsoft.Office.Interop.Excel.Application();
    if (xlApp == null)
    {
      MessageBox.Show("Excel is not installed properly!!");
      return;
    }
    try
    {
      Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(...);
      Excel.Sheets xlWorksheet = xlWorkbook.Worksheets;
      ...
      xlWorkbook.Save();
      xlWorkbook.Close();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
    xlApp.Quit();

bhs67

_{Wednesday, April 29, 2015 12:51 PM}

Hello,

To go along with the "two-dot" rule here is a complete example that properly creates and destroys objects. The code creates an excel file, removes two sheets and renames one sheet followed by populating data into the single sheet.

https://onedrive.live.com/redir?resid=a3d5a9a9a28080d1!738&authkey=!ANgRSTS5pm5pCIs&ithint=file%2czip

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

_{Wednesday, May 6, 2015 8:45 PM}

No comment on the dots-thing.  If you want to see a good tutorial on using C# to interact with Excel, check this out.

http://csharp.net-informations.com/excel/csharp-open-excel.htm

Also, look at all the links at the bottom of that page.

Good luck!!

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.