There is a common problem I see when automating Excel from an external .NET application:
You will launch Excel, open a workbook, perform work, and then try to close Excel and find that it remains in memory. You might even have code in your solution to try to re-use existing instance of Excel and find that it uses one of these “zombie” instances that do not have any of the add-ins loaded… and maybe an add-in your code relies on.
The issue is that Excel is not able to close because the primary thread of your application is holding on to a COM reference. Even adding Marshal.ComReleaseObject() and GC.Collect() like this probably will not improve the situation either:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Marshal.ReleaseComObject(excelWorkbook); | |
| Marshal.ReleaseComObject(excelApp); | |
| excelApp = null; | |
| excelWorkbook = null; | |
| GC.Collect(); | |
| GC.Collect(); | |
| GC.WaitForPendingFinalizers(); | |
| GC.WaitForFullGCComplete(); |
From my experience, and from what I have gathered is that until the thread is stopped, the COM reference will be locked. Not sure where the issue lies (EXCEL, COM, or .NET), but I have found a workaround to this. What you have to do is terminate your thread. But because you do not want to terminate your application, you need to create a NEW thread. So the following pattern is what I have found works:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| OpenFileDialog openFileDialog1 = new OpenFileDialog(); | |
| openFileDialog1.Filter = "Excel Files|*.xls*"; | |
| DialogResult result = openFileDialog1.ShowDialog(); | |
| if (result == DialogResult.OK) | |
| { | |
| new Thread(() => | |
| { | |
| Excel.Application excelApp = null; | |
| Excel.Workbook excelWorkbook = null; | |
| this.Invoke(new Action(() => | |
| { | |
| try | |
| { | |
| excelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); | |
| } | |
| catch | |
| { | |
| excelApp = new Excel.Application(); | |
| } | |
| string workbookPath = openFileDialog1.FileName; | |
| excelWorkbook = excelApp.Workbooks.Open(workbookPath); | |
| excelApp.Visible = true; | |
| })); | |
| Marshal.ReleaseComObject(excelWorkbook); | |
| Marshal.ReleaseComObject(excelApp); | |
| excelApp = null; | |
| excelWorkbook = null; | |
| openFileDialog1 = null; | |
| GC.Collect(); | |
| GC.Collect(); | |
| GC.WaitForPendingFinalizers(); | |
| GC.WaitForFullGCComplete(); | |
| }).Start(); | |
| } |