C# and Excel Interop resource problem

I am having an issue with releasing a reference to an excel spreadsheet after editing with c# code.

I open the excel file as follows:

Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.DisplayAlerts = false;
excelApp.Visible = false;
excelApp.EnableEvents = false;
Excel.Workbook workbook = excelApp.Workbooks.Open(fileName, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet) excelApp.ActiveSheet;
pipelineWS.Unprotect(AGENCY_PASSWORD);

Some data is entered into the sheet then I close excel as follows:

workbook.Save();
workbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
app.Workbooks.Close();
app.Quit();
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

However, when I look in the task manager processes the EXCEL.exe is never released.

Anyone have any clues?

Thanks in advance.
ksedranAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShannonECommented:
see below
using Excel = Microsoft.Office.Interop.Excel;
...
 
Excel.Application xlApp = null;
Excel.Workbook xlWb = null;
Excel.Worksheet grid = null;
 
try {
xlApp = new Excel.ApplicationClass();
xlApp.Visible = false;
xlWb = xlApp.Workbooks.Open(fname, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false);
grid = (Excel.Worksheet)xlWb.Worksheets[<yourSheetName>];
...
do stuff
...
xlWb.Save();
}//end try
 
finally {
 
if (xlWb != null) { xlWb.Close(Missing.Value, Missing.Value, Missing.Value); }
 
if (xlApp != null) { xlApp.Quit(); }
}//end finally			
		

Open in new window

0
ShannonECommented:
sorry, for Missing.Value you need to also include:

using System.Reflection;

0
Rory ArchibaldCommented:
What is pipelineWS in your code? and why do you switch from excelApp to app?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

ksedranAuthor Commented:
Hey rorya, sorry, that should be ws not pipelineWS.

I just changed the variable names when posting on here.
0
Rory ArchibaldCommented:
If you run the code with the Excel application visible are there any dialogs displayed at the end?
0
ksedranAuthor Commented:
There are no dialog boxes displayed at the end.

I was doing some debugging and discovered that the resources are actually released, but not until the application is closed, or a garbage collection is forced and the method finishes.

I made a tiny app that simply opens then closes an excel workbook on a button click.

private void button1_Click(object sender, EventArgs e)
        {
            string masterSpreadsheet = "C:\\Data\\Master\\MasterRecommendation 2008.xls";

            Microsoft.Office.Interop.Excel.Application masterExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            masterExcelApp.DisplayAlerts = false;
            masterExcelApp.Visible = true;
            masterExcelApp.EnableEvents = false;
            Microsoft.Office.Interop.Excel.Workbook masterWorkbook = masterExcelApp.Workbooks.Open(masterSpreadsheet, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet masterws = (Microsoft.Office.Interop.Excel.Worksheet)masterExcelApp.ActiveSheet;

            masterWorkbook.Save();
            masterWorkbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            masterExcelApp.Workbooks.Close();
            masterExcelApp.Quit();
            Marshal.ReleaseComObject(masterws);
            Marshal.ReleaseComObject(masterWorkbook);
            Marshal.ReleaseComObject(masterExcelApp);
        }

When I close the app the resources are released.

If I add a GC.Collect after the last line (Marshal.ReleaseComObject(masterExcelApp);)
then the resources are released after button_Click method is complete.

The problem is that I need the resources released before the method is complete because I am opening several sheets one after another in a loop. The resources have to be released right after the
Marshal.ReleaseComObject(masterExcelApp); line.

Anyone know how to force this?
0
Rory ArchibaldCommented:
I have read somewhere (I think in a book by Andrew Whitechapel) that you need to issue the GC.Collect twice:
 

GC.Collect();
GC.Waitforpendingfinalizers();
GC.Collect();
GC.Waitforpendingfinalizers();

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ksedranAuthor Commented:
Thanks a million rorya!!!

That works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.