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:
| 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:
| 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(); | |
| } |
