WorkbookBeforeClose event firing multiple times

Add-in Express™ Support Service
That's what is more important than anything else

WorkbookBeforeClose event firing multiple times
 
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Hello addin express team,

I am handling WorkbookBeforeClose event. Here's is my code:

private void adxExcelAppEvents1_WorkbookBeforeClose(object sender, ADXHostBeforeActionEventArgs e)
{
Excel.Workbooks workbooks = null;
Excel.Workbook workbook = null;
Microsoft.Office.Core.FileDialog dialog = null;
try
{
workbooks = ExcelApp.Workbooks;
workbook = ExcelApp.ActiveWorkbook;
string workbookName = workbook.Name;

// Handle scenarios where workbook prompts to close
// and a user cancels the close workbook event
if (!workbook.Saved)
{
DialogResult result = MessageBox.Show($"Do you want to save the changes you made to '{workbookName}'?", "Microsoft Excel", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning);
switch (result)
{
case DialogResult.Yes:
if (string.IsNullOrEmpty(workbook.Path)) // check if workbook has never been saved before
{
dialog = ExcelApp.FileDialog[MsoFileDialogType.msoFileDialogSaveAs];
dialog.InitialFileName = workbookName;
if (dialog.Show() == 0) // -1 action button, 0 cancel button
{
e.Cancel = true;
return;
}
dialog.Execute();
}
else
workbook.Save();
break;
case DialogResult.No:
workbook.Saved = true;
break;
case DialogResult.Cancel:
e.Cancel = true;
return;
}
}

if (Cache.Contains(workbook.FullName))
{
ClearAllCache(workbook.FullName);
}

if (workbooks.Count == 1)
{
DisableAddinButtons();
}
}
catch
{
// handle
}
finally
{
if (workbook != null) Marshal.ReleaseComObject(workbook);
if (workbooks != null) Marshal.ReleaseComObject(workbooks);
if (dialog != null) Marshal.ReleaseComObject(dialog);
if (RefreshTimer != null)
{
RefreshTimer.Stop();
RefreshTimer = null;
}
}
}

One of the customer using our addin reported that whenever user closes excel, they get a prompt window to save current excel file, also in addition to that, one more prompt to save changes to a file called "FUNCRES.XLA". This is seen because they have "Analysis Toolpak" addin active. When the last active workbook is closed, excel application is closed, and that is when I see the close event being fired multiple times(basically, twice extra for FUNCRES.XLA- Analysis toolpak addin). When I tested with Excel 2010, 2013, 2016, if (!workbook.Saved) returns false and messagebox is not shown multiple times. But the customers get multiple prompt windows with excel 2013 32bit, with Windows 7.

Do you have any comments?
Ranjitha
Posted 17 Mar, 2017 02:02:22 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Ranjitha,

You get the workbook using ExcelApp.ActiveWorkbook, while it may be different from the Excel.Workbook object passed to this event handler in the e.HostObject parameter.

Also, shouldn't you check if you may handle the workbook being closed? I believe there are plenty of workbooks that you won't like to handle.


Andrei Smolin
Add-in Express Team Leader
Posted 17 Mar, 2017 07:10:40 Top