Voice from the field
Donwloads: Demo Project
From many developers I hear a complain that Office application is not cooperate as expected. Yes, it is, because interaction with Office based on the COM objects. Unfortunately, developers have to strictly follow the rules to avoid ends up with following....
Yes, that is mandatory rules and help to save your time and avoid crashes in future products.
Let me describe the problem first. When you create VSTO addin it works in Excel process and if it crashed or throw an exception the exception will be gracefully handled by Office environment. Usually, often... In worst case when the exception happened whole Excel/Word/PowerPoint process will crash and nothing will stay in memory except your bleak memories about lost data.
Another case you have when the external app (e.g. console) instantiates Excel/Word/PowerPoint process by Callable Com Wrappers. In worst case you simple have one more office app process in the process list which is hidden and stuck in the memory. In worst case the target machine will get stuck, hung or down because of running out of memory or handlers.
To be honest it is some case when COM Addins and VSTO addins has a reference problem which is lead Office application say in the process list and consume memory.
Just a 5 cent of theory of CCR. On right side we have your awesome console application which is call Excel to open workbook. On left side we have COM client - which is Office Application exposed own object for you to open o work book. Let say Workbooks object with method Open(...). Then we have CCW create to marshal calls from .Net object (your app) and Excel objects (Workbooks)
Finally, through the PIA attached to your project you successfully call Application.Workbooks.Open(...) and the object Workbook which you get as result and object Application and object Workbooks create own CCW and then left out of scope your function. Finally, the recognized as garbage and collected by "garbage collector". What do you think happened with CCW? Right! They still alive and Because your COM reference was not properly released. Basically your com client is still though it is in use, but you already leave it.
Simply as our diving course captain usually says when see the coast patrol on horizon: "I love you but, I leave you"
Basically you leave the object which is make whole application thought that is in use and say in the memory.
Let' discuss the simple rules which is help you to avoid the memory problem for external application.
You can find attached demo project which is demonstrate opening 2 excel work book, fetching data and properly releasing reference. It also demonstrates subscription on ChangeSheet event. Enjoy J