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