C# how to close excel application in Excel VSTO AddIn

Question

I have created an Excel VSTO Addin which will show a message box with yes, no and cancel as button options on the close event of Current document.

I have opened 2 excel documents to edit. After I complete my edit I try to close one of them, when I click on document close button my message box will appear. If I click on "No" then all the changes should be discarded and the document should be closed. Other documents should not be closed.

This is the code I used on Don't save action

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    this.Application.WorkbookBeforeClose += Application_WorkbookBeforeClose;
}

private void Application_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
    DialogResult result = MessageBox.Show("Do you want to save changes to " + Wb.Name + "?", "Microsoft Excel ", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);

    switch (result)
    {
        case DialogResult.Yes:

            Wb.Save();
            break;
        case DialogResult.No:
            int count = this.Application.Workbooks.Count;
            if(count > 0)
            {

                if (count == 1)
                {
                    Excel.Application excel =Globals.ThisAddIn.Application;
                    Workbooks workbooks = excel.Workbooks;
                    foreach (Workbook wb in workbooks)
                    {
                        wb.Close(false, missing, missing);
                    }

                    workbooks = null;
                    excel.Quit();
                    excel = null;
                }
                else
                {
                    Wb.Close(false, missing, missing);
                }
            }
            break;

        case DialogResult.Cancel:
            Cancel = true;
            break;
    }
}

If morethan one excel document is opened for edit, then Excel vsto addin should close the particular excel docuemnent in which the close actions is performed and other documents should remain opened. My method closes the document but not closing the application. How to close the Excel Application completely? my excel appear like this

This question isn't completely clear. Are other applications than Excel + VSTO add-in involved? You say "the Excel document will be opened by an application for editing": what application and how is the Excel file opened? Please be specific as this could infuence what you should/can do. The reason Excel can't quit completely is that the code is running *in* Excel, so quitting Excel doesn't allow the code to complete. In addition, VSTo code should *never* explicitly use ReleaseComObject on the VSTO application. — Cindy Meister, Feb 12 '19 at 10:18
@CindyMeister thanks for your response. Are other applications than Excel + VSTO add-in involved? No how is the Excel file opened? I uploaded an excel document in DocuShare.I am opening the document from DocuShare to edit in edit mode. Here, I will get the base64 from a file and write the base64 as file File.WriteAllBytes(@"location", convert.FromBase64String(yourBase64String)); Now the document is created at this location, I am using Microsoft.Office.Interop.Excel.Workbook Open to open the document. — naveenkumar, Feb 12 '19 at 11:01
@CindyMeister so quitting Excel doesn't allow the code to complete -- then how do I can close the application in Excel vsto, Is there any other way to do this? — naveenkumar, Feb 12 '19 at 11:13
Where is the code running when you say you're using the "Interop" to open the document? In the VSTO project or elsewhere? What is starting the Excel application? You, as a user, this DocuShare (with which I am not familiar) or code elsewhere? — Cindy Meister, Feb 12 '19 at 12:14
Hi, let' s forget about all that I have given which is quite confusing. How to close the excel application in excel addin? I created vsto addin, on close event of document my pop will appear with save and don't save buttons. I am opening a document and edit it. when click on document close, my pop up will appear If I click on save document will be saved location. If I click on do not save , changes should not be saved and application must be closed. I know it is all possible with default excel save pop up, but I want to use my own pop-up — naveenkumar, Feb 12 '19 at 13:15

Gammaci · Answer 1 · 2019-02-12T10:21:07.990

0

Already answered on Stack Overflow, see if it helps you

As outlined above, quitting Excel requires invoking the Quit method from within VBA code.

edited Feb 12 '19 at 10:21

answered Feb 12 '19 at 10:14

Gammaci

11
2

Thanks for your answer, I tried a few possibilities here but did not work – naveenkumar Feb 12 '19 at 11:18
This is not VBA, this is C# in a Visual Studio Tools for Office add-in. Different rules apply. – Cindy Meister Feb 12 '19 at 12:12

score 0 · Accepted Answer · answered Feb 12 '19 at 20:03

The following code works for me, but it's important to keep in mind that the add-in technology wasn't really created with quitting the host application from within the add-in. So, "your mileage may vary".

Unlike the code in the question, this does not use Marshal.ReleaseComObject. This method is a "last resort" and forces an immediate release of the object. Once an object has been force-released the code can no longer work with it - but VSTO needs to do its internal clean-up. The method should only be used if the standard methods of releasing COM objects aren't doing the job. VSTO, in any case, as part of its "service" to the developer, takes care of standard releasing of COM objects properly declared and instantiated in the project. So it's enough, really, to set objects to null when they're no longer needed, which releases them for standard garbage collection. When VSTO goes out-of-process the objects will be released at the COM level, at the very latest.

    private void btnQuitExcel_Click(object sender, RibbonControlEventArgs e)
    {
        Excel.Application xlApp = Globals.ThisAddIn.Application;
        Excel.Workbooks wbs = xlApp.Workbooks;
        int nrWbs = wbs.Count;
        if (nrWbs > 0)
        {
            foreach (Excel.Workbook wb in wbs)
            {
                wb.Close(false, missing, missing);
            }
        }
        wbs = null;
        xlApp.Quit();
        xlApp = null;
    }

thanks for your answer. your answer works fine but closes all the documents. Consider, I have opened 2 documents to edit and edited 2 documents. If I close the first document, my pop up appears and if I click do not save(no) it actually closes the active document which is fine. But it also closes another document without showing pop. We can not close all documents in for each loop when I need to close only active document. — naveenkumar, Feb 13 '19 at 05:04
@naveenkumar That requirement was not part of the question, nor of the comments when I asked for details. How should anyone know that? The impression the question gives is just the opposite. Simply change the suggested code to accomodate what you actually need. The important part is to quit when there are no longer any documents open and to release the objects correctly. — Cindy Meister, Feb 13 '19 at 07:43

C# how to close excel application in Excel VSTO AddIn

2 Answers2