Excel-DNA add-in unloads when"Do you want to save prompt" cancel button is selected

Question

When the I press File -> Exit (or Alt+F4) or the X, I got prompted with the "Do you want to save changes" dialog, where I can pick "Cancel". So Excel can continue, but my excel-dna add-in is never notified again and is left unloaded.

Thanks in advance guys!

Answer 1

Armando! Thanks a lot for your help!

I've found that there's a IDTExtensibility2 Interface that hosts event notifications that occur to add-ins, such as when they are loaded, unloaded, updated, and so forth. So I use that interface with ExcelComAddIn Class in namespace ExcelDna.Integration:

public class ExcelComAddIn : IDTExtensibility2
{
    public ExcelComAddIn();

    protected string ProgId { get; }

    public virtual void OnAddInsUpdate(ref Array custom);
    public virtual void OnBeginShutdown(ref Array custom);
    public virtual void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom);
    public virtual void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom);
    public virtual void OnStartupComplete(ref Array custom);
}

I noticed that OnBeginShutdown() Method runs AFTER the dialog prompt! and that's what I was looking for, so I got rid of the WorkbookBeforeClose event, I override the OnBeginShutdown() Method and I put my code that were in WorkbookBeforeClose event into OnBeginShutdown() like this:

public override void OnBeginShutdown(ref Array custom)
{
    base.OnBeginShutdown(ref custom);

    //I PUT MY CUSTOM CODE HERE:
    CloseAllPanes();

    ExcelTaskExecutor.Destroy();
}

And now if the user choose to click "cancel" on the save dialog, the OnBeginShutdown() does not run and my panes are still there!

Anyways, your approaches are very cool though and If it wasn't by your help I never had had the chance to figure out what was going on and thanks for putting me on track to find this solution.

Answer 2

As far as I understand the problem occurs when the WorkbookBeforeClose executes before the user clicks cancel. There are some alternatives.

The easy one is that you can save the active woorkbook in the WorkbookBeforeClose event (the woorkbook object has a methods save and saveas save). Since the workbook is already saved, the save dialog won't show and the user won't click the cancel button.

Another solution is to invoke a custom save dialog in the WorkbookBeforeClose. I've used the following code in other projects. The WorkbookBeforeClose might look like this:

  private void ActiveWorkbook_BeforeClose(ref bool Cancel)
    {
        DefaultSaveExcel(Excel.ActiveWorkbook,ref Cancel);

        if (!Cancel)
        {
            //if enters here is because the workbook is actually closing
            Delete(Excel.ActiveWorkbook.Name);
        }                     
    }

The actual DefaultSaveExcel implementation might look like this:

 public  void DefaultSaveExcel(Workbook wb, ref bool Cancel)
    {
        while (wb.Saved == false && Cancel == false)
        {
            var result = ShowMessageDialogSave();

            if (result == System.Windows.Forms.DialogResult.Yes)
            {
                var sa = CreateExcelSaveDialog(wb.FullName);

                if (sa.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    wb.SaveAs(sa.FileName);
                    wb.Save();

                }
            }
            else if (result == System.Windows.Forms.DialogResult.No)
            {
                wb.Saved = true;
            }
            else if (result == System.Windows.Forms.DialogResult.Cancel)
            {
                Cancel = true;
            }
        }
    }

    public  System.Windows.Forms.SaveFileDialog CreateExcelSaveDialog(string name = null)
    {
        var sa = new System.Windows.Forms.SaveFileDialog();
        sa.Filter = "Excel files (*.xlsx)|*.xlsx";
        if (!string.IsNullOrEmpty(name))
            sa.FileName = name;
        sa.CreatePrompt = true;

        return sa;
    }

    public  DialogResult ShowMessageDialogSave()
    {
        var app = (Microsoft.Office.Interop.Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
        NativeWindow xlMain = new NativeWindow();
        xlMain.AssignHandle(new IntPtr(app.Hwnd));

        var message = "Do you want to save pending changes?";

        if (Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName.ToLower() == "es")
            message = "¿Desea guardar los cambios pendientes?";


        return System.Windows.Forms.MessageBox.Show(xlMain, message, "Microsoft Excel", System.Windows.Forms.MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning,MessageBoxDefaultButton.Button1);
    }

Hope this helps, Armando