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

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

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.

Just what do you mean by kill ?
Is Excel busy and not responding in a eg. calculation / running a macro.....

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.

@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.

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

Gerald

What order are you quitting/disposing of things?

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();

Gerald

That looks fine to me apart from this.

  app1.Application.Quit();
  app1.Quit();

Select allOpen 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.

Yes Norie. Please see below and appreciate the timely feedback.

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

Gerald

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.

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?

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

Thanks Andy! I thought so but saw something while troubleshooting and gave it a try.

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.

Will do! Trying now.

why you putting:

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

Select allOpen in new window

in the loop below?

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

Select allOpen in new window

I have moved inside and outside the loop. It was just a test.

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?

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.

Gerald

Will the Access database be in a different folder on each machine?

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.

Will do.

do you need further clarification or shall we proceed to close this question?