Excel References Not Release When IDTExtensibility2 Used

[patstix]

My C# add-in implements IDTExtensibility2. As long as I don't sync my
add-in to any Excel events, it works perfectly and the Excel instance
is removed when Excel is shut down.

However, my add-in must listen to some Excel events. I hook them up
during OnConnection, like so (using any of the Excel Workbook events
gives the same problem):
applicationObject.WorkbookOpen += new
Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(applicationObject_WorkbookOpen);
Once I add this line of code, the Excel instance sticks around, even
after Excel is terminated and released. Removing the delegate from
the event (and releaseing the COM reference) in the OnDisconnection
method does not help.

Using .Net 2.0 and the issue exists with at least Office XP and 2003.

The code used to test the creation and release of the Excel instance
is here:
Sub Test()
Set oXL = CreateObject("Excel.Application")
Set WBs = oXL.Workbooks
Set Wkbk = WBs.Add
Set Wkbk = Nothing
Set WBs = Nothing
oXL.Quit
Set oXL = Nothing
End Sub

 

[Joel]

You need to close your workbook. Not sure if youwill get an error with
savechanges. My need to eliminate Savechanges.

Sub Test()
Set oXL = CreateObject("Excel.Application")
Set WBs = oXL.Workbooks
Set Wkbk = WBs.Add
Wkbk.Close savechanges:=False
Set Wkbk = Nothing
Set WBs = Nothing
oXL.Quit
Set oXL = Nothing
End Sub

 

[patstix]

You need to close your workbook.  Not sure if youwill get an error with
savechanges.  My need to eliminate Savechanges.

Sub Test()
    Set oXL = CreateObject("Excel.Application")
    Set WBs = oXL.Workbooks
    Set Wkbk = WBs.Add
    Wkbk.Close savechanges:=False
    Set Wkbk = Nothing
    Set WBs = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

Joel - that makes no difference. The instance of Excel disappears
correctly as long as I don't connect to any Excel events in the
OnConnection method. The VB is fine.

 

[Joel]

When you open an event it returns the Process ID number. You need to KILL
that process. Not sure the format of the instructions.

You Should be able to do something like this

ProcID =
Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(applicationObject_WorkbookOpen);

The ProcID will be the same number that you would see in the Task Manager.
If you don't see the PROCID in the task manager you need to add a column in
the Menu View - Select Columns

 

[patstix]

Joel - I'm not sure I understand your last post - see here: it seems
an event handler is what gets returned, not a ProcID.

Excel.AppEvents_SheetSelectionChangeEventHandler
EventDel_SheetSelectionChange;

OnConnection(...){
EventDel_SheetSelectionChange = new
AppEvents_SheetSelectionChangeEventHandler(API.Application_SheetSelectionChange);
m_Excel.SheetSelectionChange += EventDel_SheetSelectionChange;
}

OnDisconnection(...){
m_Excel.SheetSelectionChange -= EventDel_SheetSelectionChange;
Marshal.ReleaseComObject(EventDel_SheetSelectionChange);
EventDel_SheetSelectionChange = null;
}

 

[patstix]

Joel - Certainly killing the process would stop it from hanging
around, but that hardly is a proper solution - if the process goes
away by itself so long as I don't hook into an Excel application
event, then clearly there is something that needs to be released
related to the evtn handling that would produce the same result.
Killing the process when teh add-in is disconnected is incredibly
dangerous; for example, what if the user just wishes to remove that
add-in, but keep Excel open?

 

[Joel]

You need to use CloseEvent and pass it the Handle Number.

 

[patstix]

Joel - can you clarify that please? How do I "use" CloseEvent? If
you mean the workbook close event, then what should I do there with
the handle number? As you can see, I'm already removing the
registration of the handler.
thanks!

 

[Joel]

A handle is a linked lists of event that is usually in windows triggered by
the Tick timer. When a timer event occurs windows goes down the list of
events and runs each process. When you stop a handle you need to remove the
handle from the link list of events and kill the process. The closeEvent I
belive does both operations, unlink the event and stops the process.


Description of what a handle is
http://msdn.microsoft.com/en-us/library/ms724176(VS.85).aspx

closeevent function
http://msdn.microsoft.com/en-us/library/ms724211.aspx

 

[patstix]

Joel - you said "You need to use CloseEvent and pass it the Handle
Number. "
You then again reference the "closeevent function" in your follow up
post, but link to a function that closes the handle, called
CloseHandle. Was that just a mistake on the name of the function, or
did you mean to link something else?
thanks!

 

[Joel]

I said closeevent instead of closehandle. They are tow different things.
The close Event is trigger if you close an open window or other handle event.
The close Handle shuts down the the process.

Handle really stands for Interupt Handler. The Handles in Windows are
usually timer driven but some are hardware trigered or combination of both
hardware and timer. When you press on the mouse key it triggers a hardware
event but that event is process during one of the Timer Tick Interupts.

 

[Peter T]

I don't know C# but I'd look further into this bit

Removing the delegate from the event
(and releaseing the COM reference) in the OnDisconnection
method does not help.

maybe you have more than one reference that you have not released (not
necessarily the app)

Regards,
Peter T

 

[patstix]

Peter - I agree that, in theory that would be causing the problem; I
just don't know what I could do differently in my code - again, here
are the code snippets:
Excel.AppEvents_SheetSelectionChangeEventHandler
EventDel_SheetSelectionChange;

OnConnection(...){
EventDel_SheetSelectionChange = new
AppEvents_SheetSelectionChangeEventHandler(API.Application_SheetSelectionChange);
m_Excel.SheetSelectionChange += EventDel_SheetSelectionChange;
}

OnDisconnection(...){
m_Excel.SheetSelectionChange -= EventDel_SheetSelectionChange;
Marshal.ReleaseComObject(EventDel_SheetSelectionChange);
EventDel_SheetSelectionChange = null;
}