Automating Excel and Add-ins Don’t load


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

view raw
COMRelease.cs
hosted with ❤ by GitHub

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

view raw
ExcelWorkaround.cs
hosted with ❤ by GitHub

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s