Trapping Excel Close event

[Davo]

Hi, from what I can gather reading previous posts, the
IExcelAddIn.AutoClose event is triggered only when the xll add-in is
closed. Can you please advise if there is a way to trap when Excel
closes? I am keeping stats on how many times each UDF is used, but I
don't want to do the logging each time - I'd rather store them up then
write to the log at the end of the session. Many thanks.

[Govert van Drimmelen]

Hi,

Excel-DNA will call the AutoClose function in your add-in's
IExcelAddIn class when the add-in is removed from the add-ins dialog
(Alt+t,i) by the user, or if the add-in is reloaded. In this case you
can properly clean up your add-in - remove menus etc.
Mostly when Excel shuts down you would not want to do a lot of clean-
up - no need to remove menus, deregister functions etc.

In your case, I can make some suggestions for how to reliably get
notified of the Excel shutdown:

* If you are running in Excel 2007+ and have an ExcelRibbon-derived
class, just override the OnDisconnection or OnBeginShutdown.

* To target any Excel version, add a new class that derives from
ExcelComAddin, load it in your AutoOpen with
ExcelComAddInHelper.LoadComAddIn(...), and override the
OnDisconnection or OnBeginShutdown.

Note that There was a bug in the released version of Excel-DNA 0.29
that prevented ExcelComAddInHelper.LoadComAddIn(...) from running
properly - see http://groups.google.com/group/exceldna/browse_frm/thread/143bd77dec8e6579.
There is an ugly workaround using reflection, which the current source
version of Excel-DNA (which you can try from
http://exceldna.codeplex.com/SourceControl/list/changesets) should not
need.

I paste below a .dna file which has the workaround and should work on
the Excel-DNA v0.29 release.

Regards,
Govert



<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using SWF = System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration.Extensibility;

[ComVisible(true)]
public class MyCom : ExcelDna.Integration.CustomUI.ExcelComAddIn
{

public MyCom()
{
}
public override void OnConnection(object Application,
ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
{
SWF.MessageBox.Show("OnConnection");
}
public override void OnDisconnection(ext_DisconnectMode
RemoveMode, ref Array custom)
{
SWF.MessageBox.Show("OnDisconnection");
}
public override void OnAddInsUpdate(ref Array custom)
{
SWF.MessageBox.Show("OnAddInsUpdate");
}
public override void OnStartupComplete(ref Array custom)
{
SWF.MessageBox.Show("OnStartupComplete");
}
public override void OnBeginShutdown(ref Array custom)
{
SWF.MessageBox.Show("OnBeginShutDown");
}
}
public class AddIn : IExcelAddIn
{
private ExcelComAddIn com_addin;
public AddIn()
{
}
public void AutoOpen()
{
try
{
com_addin = new MyCom();

// To work around the v0.29 bug, we want to do this:
// com_addin.DnaLibrary =
ExcelDna.Integration.DnaLibrary.CurrentLibrary;
// But the DnaLibrary property is marked 'internal'
// to ExcelDna.Integration, so we use Reflection.
// Just remove this call for recent Excel-DNA
versions.
com_addin.GetType().InvokeMember("DnaLibrary",
BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.SetProperty,
null, com_addin,
new object[] {DnaLibrary.CurrentLibrary});

ExcelComAddInHelper.LoadComAddIn(com_addin);
}
catch (Exception e)
{
SWF.MessageBox.Show("Error loading COM AddIn: " +
e.ToString());
}
}
public void AutoClose()
{
}
}
]]>
</DnaLibrary>

[Davo]

Thanks Govert, that worked brilliantly.

On Mar 30, 6:44 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> Excel-DNA will call the AutoClose function in your add-in's
> IExcelAddIn class when the add-in is removed from the add-ins dialog
> (Alt+t,i) by the user, or if the add-in is reloaded. In this case you
> can properly clean up your add-in - remove menus etc.
> Mostly when Excel shuts down you would not want to do a lot of clean-
> up - no need to remove menus, deregister functions etc.
>
> In your case, I can make some suggestions for how to reliably get
> notified of the Excel shutdown:
>
> * If you are running in Excel 2007+ and have an ExcelRibbon-derived
> class, just override the OnDisconnection or OnBeginShutdown.
>
> * To target any Excel version, add a new class that derives from
> ExcelComAddin, load it in your AutoOpen with
> ExcelComAddInHelper.LoadComAddIn(...), and override the
> OnDisconnection or OnBeginShutdown.
>
> Note that There was a bug in the released version of Excel-DNA 0.29
> that prevented ExcelComAddInHelper.LoadComAddIn(...) from running

> properly - seehttp://groups.google.com/group/exceldna/browse_frm/thread/143bd77dec8....

> There is an ugly workaround using reflection, which the current source

> version of Excel-DNA (which you can try fromhttp://exceldna.codeplex.com/SourceControl/list/changesets) should not

[Xavier Aymé - Mauchand]

Hi again Govert,

Is this solution still up to date or do you think of something which may be easier to implement? I need to serialize a couple of classes before exiting. 

Actually, when I tried to implement it, the compiler tells me that ExcelComAddIn class cannot be found, am I missing any package. 

Have a nice day, 

Xavier