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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
AndyAinscowFreelance programmer / ConsultantCommented:
Just what do you mean by kill ?
Is Excel busy and not responding in a eg. calculation / running a macro.....
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

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
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
NorieAnalyst Assistant Commented:
Gerald

Will the Access database be in a different folder on each machine?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you need further clarification or shall we proceed to close this question?
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
C#

From novice to tech pro — start learning today.