Releasing Excel file after creating and editing it

Question

I am creating an Windows Form application that can generate Excel files from database data. After filling the database into the file i am opening the it and executing a macro, saving the file and then closing it (or i think i am closing it). These files are saved on my HDD but When all the files are generated i have to be able to generate a new set of files and therefor i have to delete all the old files from my HDD before generating the new set. After running the application once and i try to generate the new set of files, i get an error stating that one of the files (the last file that have been generated in the first batch) is used by another process.

here is my code:

public void RemoveRows_Macro(string fileName)
{

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    xlWorkBook = xlApp.Workbooks.Open(fileName);
    //xlApp.Visible = true;
    xlApp.DisplayAlerts = true;

    //Run the macro
    xlApp.Run("RemoveRows", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    xlWorkBook.Save();

    xlWorkBook.Close(false);
    xlApp.Quit();
    releaseObject(xlApp);
    releaseObject(xlWorkBook);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error in releasing object :" + ex);
        obj = null;
    }
    finally
    {
        GC.Collect();
    }
}

What am i doing wrong since the application is not releasing the file ?

score 2 · Answer 1 · edited May 23 '17 at 12:04

2

You would want to release the objects first and then set them as null in the reverse order of how you assigned them.

releaseObject(xlWorkBook);
releaseObject(xlApp);
xlWorkBook = null;
xlApp = null;

I found this link a great resource when I was doing Excel Interop How do I properly clean up Excel interop objects?

edited May 23 '17 at 12:04

Community

1
1

answered Mar 19 '13 at 08:22

jordanhill123

4,142
2
31
40

Thanks for the link. appreciate the help – Lahib Mar 19 '13 at 08:28

score 1 · Accepted Answer · answered Mar 19 '13 at 08:19

1

 finally
{
    GC.Collect(); 
   GC.WaitForPendingFinalizers();
}

answered Mar 19 '13 at 08:19

TalentTuner

17,262
5
38
63

Sorry i mistyped! im trying it now. – Lahib Mar 19 '13 at 08:20

score 0 · Answer 3 · answered Mar 19 '13 at 08:16

0

I can't run a test just now, but it looks like you are setting the xlApp and xlWorkBook to null before passing them to releaseObject. There might be other reasons after that.

answered Mar 19 '13 at 08:16

Joe Stein

31
4

I have removed the 2 lines and it still does not release the last generated file. – Lahib Mar 19 '13 at 08:17

score 0 · Answer 4 · answered Mar 19 '13 at 08:17

0

You first set the xlApp and xlWorkBook to null and then you try to release it. Try to comment out these two lines:

xlApp = null;
xlWorkBook = null;

Does this fix the problem?

answered Mar 19 '13 at 08:17

Mitja Bezenšek

2,503
1
14
17

Tried that right away. and it didnt fix the problem. it still locks the last generated file – Lahib Mar 19 '13 at 08:18

score 0 · Answer 5 · edited Jul 08 '20 at 08:46

0

I got the same problem, but I fixed it. So, after I created the file, I close it.

xlWorkbook.SaveAs("nameExcel");
xlWorkbook.Close();

edited Jul 08 '20 at 08:46

David Buck

3,752
35
31
35

answered Jul 08 '20 at 08:02

Jeki Gates

1
1
5

Releasing Excel file after creating and editing it

5 Answers5