C# Visual Studio question regarding how to kill Excel interop process.

C# Visual Studio question regarding how to kill Excel interop process.
Gerald JonesAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
regardless the operation of the Excel objects, I'm using the codes below to kill the Excel interop process.

wb1.Close(true, Type.Missing, Type.Missing);
            NAR(wb1);
            app1.Quit();
            NAR(app1);

            GC.Collect();
            GC.WaitForPendingFinalizers();

Open in new window


with function from my first post:

private void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o);
                // Or using
                 //while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) ;
            }
            catch { }
            finally
            {
                o = null;
            }
        }

Open in new window

0
 
Ryan ChongCommented:
check this out:

Office application does not quit after automation from Visual Studio .NET client
https://support.microsoft.com/en-us/help/317109/office-application-does-not-quit-after-automation-from-visual-studio
0
 
Dirk StraussSenior Full Stack DeveloperCommented:
Hey Gerald

Have a look at this article as well:  How to properly release Excel COM objects.

Having said that, have you considered using EPPlus. You can get EPPlus from NuGet. If you need data from Excel, you can use EPPlus and then Excel does not have to be installed on the machine you run your application on. EPPlus is available on GitHub.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AndyAinscowFreelance programmer / ConsultantCommented:
Just what do you mean by kill ?
Is Excel busy and not responding in a eg. calculation / running a macro.....
0
 
Dirk StraussSenior Full Stack DeveloperCommented:
I think what Gerald means is that after the application has completed, that the object still shows up in Task Manager and is not disposed of. It might also still have a lock on the Excel file that was worked with.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
@Dirk.  My guess is your assumption is correct BUT I would rather have the asker confirm that is the problem and not something else.  If the assumption is incorrect then people end up just wasting time.
0
 
Gerald JonesAuthor Commented:
Hello All:

Apologies for any confusion! Dirk has summarized completely my question and has also mentioned my problem with the file being locked also. To summarized:

The application has completed, that the object still shows up in Task Manager and is not disposed of. The application definitely still has  a lock on the Excel file that was worked with. I'm not sure if the order disposing/quitting everything is respected in my code.

I look forward to any help you can provide, definitely willing to schedule a live session and will take a quick look at the EPPlus. I am in a little bit of a time crunch which is why I reached out to you. Your help is greatly appreciated.

Gerald
0
 
NorieVBA ExpertCommented:
Gerald

What order are you quitting/disposing of things?
0
 
Gerald JonesAuthor Commented:
ws1.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, (appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xps"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    wb1.Close(true, Type.Missing, Type.Missing);
                    app1.Application.Quit();
                    app1.Quit();

                    // Manual disposal because of COM
                    while (Marshal.ReleaseComObject(app1) != 0) { }
                    while (Marshal.ReleaseComObject(ws1) != 0) { }
                    Marshal.ReleaseComObject(wb1);


                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                }

                wb1 = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
0
 
NorieVBA ExpertCommented:
Gerald

That looks fine to me apart from this.
  app1.Application.Quit();
  app1.Quit();

Open in new window

Assuming app1 is a reference to an instance of Excel you should only need to quit it once using the 2nd line of code.
0
 
Gerald JonesAuthor Commented:
Yes Norie. Please see below and appreciate the timely feedback.

                Microsoft.Office.Interop.Excel.Application app1 = new Microsoft.Office.Interop.Excel.Application();

Gerald
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
In theory this code is redundant:
                    // Manual disposal because of COM
                    while (Marshal.ReleaseComObject(app1) != 0) { }
                    while (Marshal.ReleaseComObject(ws1) != 0) { }
                    Marshal.ReleaseComObject(wb1);


                    GC.Collect();
                    GC.WaitForPendingFinalizers();


unless you have specific reasons for performing a manual cleanup.
0
 
NorieVBA ExpertCommented:
Gerald

The problem might actually lie in your earlier code where, presumably, you are doing something with the instance of Excel, e.g. manipulating workbooks/sheets etc.

What is it you are doing with the code?
0
 
Gerald JonesAuthor Commented:
Norie,
Apologies for the long delay. I was buried in code. This is all that I'm doing. Please see below:

                Microsoft.Office.Interop.Excel.Application app1 = new Microsoft.Office.Interop.Excel.Application();

                app1.Visible = false;
                app1.DisplayAlerts = false;

                ReportsViewModel vm = (panelAnalysis.DataContext as ReportsViewModel);
                FileInfo fi = vm.ReportFileNamesView.CurrentItem as FileInfo;

                var wb1 = app1.Workbooks.Open(appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xlsx", UpdateLinks: 3);
               
                wb1.RefreshAll();
               
                foreach (Microsoft.Office.Interop.Excel.Worksheet ws1 in wb1.Worksheets)
                {

                    string Label_str1 = " ";
                    string Label_str2 = " ";

                    if (datePicker1_str.ToString("MM/dd/yyyy") != "01/01/1977") { Label_str1 += "    Beginning Report Date: " + datePicker1_str.ToString("MM/dd/yyyy"); }
                    if (datePicker2_str.ToString("MM/dd/yyyy") != "01/01/2050") { Label_str2 += "     Ending Report Date: " + datePicker2_str.ToString("MM/dd/yyyy"); }

                    object misValue = System.Reflection.Missing.Value;
                    ws1.Cells[7, 4] = Label_str1;
                    ws1.Cells[8, 4] = Label_str2;
                    wb1.SaveAs(appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    // Get the read-only value for a file.
                    FileInfo fi1 = new FileInfo("ProjectsbyCohort.xps");
                    bool IsReadOnly = fi1.IsReadOnly;
                    if (IsReadOnly == true)
                    {
                        documentViewer1.Document = null;

                    }

                    ws1.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, (appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xps"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    wb1.Close(true, Type.Missing, Type.Missing);
                    app1.Application.Quit();
                    app1.Quit();


Many thanks,

Gerald
0
 
Gerald JonesAuthor Commented:
Thanks Andy! I thought so but saw something while troubleshooting and gave it a try.
0
 
NorieVBA ExpertCommented:
Gerald

Have you tried making the Excel instance visible and then stepping through the code to see what's happening in it?

It could be that, for some reason, Excel isn't quitting when you tell it to.
0
 
Gerald JonesAuthor Commented:
Will do! Trying now.
0
 
Ryan ChongCommented:
why you putting:

wb1.Close(true, Type.Missing, Type.Missing);
                    app1.Application.Quit();
                    app1.Quit();

Open in new window


in the loop below?

foreach (Microsoft.Office.Interop.Excel.Worksheet ws1 in wb1.Worksheets)

Open in new window

0
 
Gerald JonesAuthor Commented:
I have moved inside and outside the loop. It was just a test.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
I suspect the displaying excel will result in you seeing it is waiting for user input.

Do the files you save actually appear in the directory you expect them to be saved into?
0
 
Gerald JonesAuthor Commented:
Ryan, Andy, Norie,

Per your support, I added the code above and it's working. The Excel process is closing appropriately. Many thanks for to all for your support.

One last question:
How to specify a path/location of the MS Access database using Excel interop for the Excel query that queries an MS Access database?

When install the app on a different machine, the query can no longer find the database. Please help and many thanks.
0
 
NorieVBA ExpertCommented:
Gerald

Will the Access database be in a different folder on each machine?
0
 
Ryan ChongCommented:
How to specify a path/location of the MS Access database using Excel interop for the Excel query that queries an MS Access database?
is that mean your excel is linked to an Access database to populate the data? this could be a new long discussion, i would suggest to create another new question for discussion since it not really related to what you have asked initially.
0
 
Gerald JonesAuthor Commented:
Will do.
0
 
Ryan ChongCommented:
do you need further clarification or shall we proceed to close this question?
0
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.

All Courses

From novice to tech pro — start learning today.