How to properly release Excel COM objects
You’ll see a lot of complaints on the internet about Excel and other Microsoft Office applications not quitting properly after using the object model to perform certain actions, or showing sporadic and unpredictable behavior in COM add-ins. In the end most of these issues boil down to developers not properly disposing of COM objects.
The challenge is that despite the fact that, as .Net developers we can use the Office Interop Assemblies to access the various Office object models using managed code, the PIA’s are still essentially wrapping around COM objects. So what is the problem?
- A general rule of thumb
- For or ForEach Loops
- ReleaseComObject and FinalReleaseComObject?
- GC.Collect and GC.WaitForPendingFinalizersr
- Ways to kill the Excel.exe process
A general rule of thumb
Our very own Andrei Smolin wrote two great articles explaining the reasons behind why Excel does not quit and when to release COM objects in .Net. Both articles contain a lot of very useful and thorough advice and you’ll notice that it all starts with a simple rule:
‘1 dot good, 2 dots bad’
I’m sure you’re staring at your screen with wide-eyed confusion at the moment, but this rule is actually very easy to explain. Consider the following code:
Excel.Application app = new Excel.Application(); Excel.Workbook book = app.Workbooks.Add(); Excel.Worksheet sheet = app.Sheets.Add(); sheet.Range["A1"].Value = "Lorem Ipsum"; book.SaveAs(@"C:\Temp\ExcelBook.xlsx"); book.Close(); app.Quit();
The code above will build and run without a problem, it will create a new Excel workbook, add a new sheet and set the value of the first cell in the newly created sheet. However, even after calling the Quit method of the Excel.Application object, you’ll still see the Excel.exe process in the Windows Task Managers’ list of background processes.
This strange phenomenon occurs because in the above code, we’re not releasing any COM objects and we’re also “chaining” object references by using double dots. You must ALWAYS release COM objects, even if you see no adverse effects, it might work perfectly on your PC but behave entirely different on a user’s computer.
Let’s look at how to change the code in order to safely dispose of any COM objects:
Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; Excel.Worksheet sheet = null; Excel.Range range = null; try { app = new Excel.Application(); books = app.Workbooks; book = books.Add(); sheets = book.Sheets; sheet = sheets.Add(); range = sheet.Range["A1"]; range.Value = "Lorem Ipsum"; book.SaveAs(@"C:\Temp\ExcelBook" + DateTime.Now.Millisecond + ".xlsx"); book.Close(); app.Quit(); } finally { if (range != null) Marshal.ReleaseComObject(range); if (sheet != null) Marshal.ReleaseComObject(sheet); if (sheets != null) Marshal.ReleaseComObject(sheets); if (book != null) Marshal.ReleaseComObject(book); if (books != null) Marshal.ReleaseComObject(books); if (app != null) Marshal.ReleaseComObject(app); }
Pay close attention to the above code, we never used more than one dot when working with objects. We also wrapped all the code in a try-finally, so even if the code throws and exception we will still safely release the COM objects using the ReleaseComObject method on the Marshal object.
For or ForEach Loops
There is no obvious reason why you should use a for-loop rather than a ForEach loop, however it is recommended that you rather use a for loop since a for-each might cause some unexpected behavior and your code to hang. Consider the following code:
Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; try { app = new Excel.Application(); books = app.Workbooks; book = books.Open(@"C:\Temp\ExcelBook.xlsx"); sheets = book.Sheets; foreach (Excel.Worksheet sheet in sheets) { Console.WriteLine(sheet.Name); Marshal.ReleaseComObject(sheet); } book.Close(); app.Quit(); } finally { if (sheets != null) Marshal.ReleaseComObject(sheets); if (book != null) Marshal.ReleaseComObject(book); if (books != null) Marshal.ReleaseComObject(books); if (app != null) Marshal.ReleaseComObject(app); }
In the above code, everything appears to be fine. We did not use more than one dot and we safely released all COM objects using a try-finally code clock. However, using a foreach loop to loop through the Sheets collection of the Excel.Workbook object automatically generates the enumerator behind the foreach statement that uses an internal COM object, which needs to be released..
To be on the safe side, you should avoid using a foreach loop and rather use a normal for loop, and release each COM object in the collection, as illustrated below:
Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; try { app = new Excel.Application(); books = app.Workbooks; book = books.Open(@"C:\Temp\ExcelBook1Sheets.xlsx"); sheets = book.Sheets; for (int i = 1; i <= sheets.Count; i++) { Excel.Worksheet sheet = sheets.Item[i]; Console.WriteLine(sheet.Name); if (sheet != null) Marshal.ReleaseComObject(sheet); } book.Close(); app.Quit(); } finally { if (sheets != null) Marshal.ReleaseComObject(sheets); if (book != null) Marshal.ReleaseComObject(book); if (books != null) Marshal.ReleaseComObject(books); if (app != null) Marshal.ReleaseComObject(app); }
ReleaseComObject & FinalReleaseComObject?
When you access an Office COM object via the interop assemblies, the .Net framework automatically wraps it in a Runtime Callable Wrapper, the RCW object is also responsible for controlling the objects’ lifetime.
Keep in mind that the .Net runtime creates one RCW for each COM object. So, no matter how many references you have to a specific COM object, there will always be just one Runtime Callable Wrapper for it. As you create more references to a certain COM object the RCW’s reference count will increase and this is where the ReleaseComObject and FinalReleaseComObject come into play.
Both methods are used to release references to a RCW, ReleaseComObject simply decreases the reference count of a specific RCW, whereas FinalReleaseComObject releases ALL references to the RCW and sets the reference count to zero.
Essentially, calling FinalReleaseComObject would be similar to creating a for-loop and calling ReleaseComObject until its reference count is zero. When the reference count is zero, it means the object is ready to be garbage collected.
Both methods need to be used with a relative degree of caution, if you release a COM object and try to access it afterwards an InvalidComObjectException will be shown with the following message:
“COM object that has been separated from its underlying RCW cannot be used”
As a rule we never use FinalReleaseComObject, by calling ReleaseComObject the reference counter should be decreased and if everything is correct the COM object should be properly released with a single call. FinalReleaseComObject is redundant and might cause unexpected results and a whole lot of pain.
GC.Collect & GC.WaitForPendingFinalizers
The generally accepted best practice is not to force a garbage collection in the majority of cases; however, you can release COM objects using the .Net garbage collector, as long as there are no references to the objects. In other words, the objects are set to null. Be aware that GC.Collect can be a time consuming process depending on the number of objects.
You would also need to call GC.Collect and GC.WaitForPendingFinalizers twice when working with Office COM objects since the first time you call the methods we only release objects that we are not referencing with our own variables. The second time the two methods are called is because the RCW for each COM object needs to run a finalizer that actually fully removes the COM Object from memory.
So, it is totally acceptable to see the following code in you COM add-in projects:
GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers();
Ways to kill the Excel.exe process
WM_CLOSE
Of course, there are ways to kill the Excel process if you have to. One such way is to send a WM_CLOSE message to the Excel windows in order for it to terminate. First, you’ll need to use the DLLImport attribute to invoke the SendMessage method contained in the user32 dll. Do this by adding the following code at the top of your class:
[DllImport("user32.dll", CharSet = CharSet.Auto)] private static extern IntPtr SendMessage(IntPtr hWnd, int msg, IntPtr wParam, IntPtr lParam);
The trickiest part of this is to get the window id or hWnd of the main Excel window. Luckily, if you’ve accessed the Excel object model you can retrieve the main windows’ hWnd by checking the Hwnd property on the ActiveWindow object. The ActiveWindow object is a property on the Excel.Application object. If the Excel version you’re targeting does not have the Hwnd property on the Application object, you can use late-binding to access it, as illustrated below.
Excel.Application app = new Excel.Application(); hWnd = app.Hwnd;
After you’ve retrieved the hWnd value, call the SendMessage method as indicated below, to force the main Excel window to close:
SendMessage((IntPtr)hWnd, 0x10, IntPtr.Zero, IntPtr.Zero);
Process.Kill
An easier way kill all Excel processes, is to use the Kill method of the .Net Process object. The object can be found in the System.Diagnostics namespace. The following code will retrieve all the Excel processes and kill each one:
Process[] excelProcs = Process.GetProcessesByName("EXCEL"); foreach (Process proc in excelProcs) { proc.Kill(); }
Windows Job Objects
Lastly, you could also use the Windows Job Objects to properly dispose of the Excel process even if it takes a bit more work to get going. First you need to add a new class to your project:
namespace JobManagement { public class Job : IDisposable { [DllImport("kernel32.dll", CharSet = CharSet.Unicode)] static extern IntPtr CreateJobObject(IntPtr a, string lpName); [DllImport("kernel32.dll")] static extern bool SetInformationJobObject(IntPtr hJob, JobObjectInfoType infoType, IntPtr lpJobObjectInfo, UInt32 cbJobObjectInfoLength); [DllImport("kernel32.dll", SetLastError = true)] static extern bool AssignProcessToJobObject(IntPtr job, IntPtr process); [DllImport("kernel32.dll", SetLastError = true)] static extern bool CloseHandle(IntPtr hObject); private IntPtr handle; private bool disposed; public Job() { handle = CreateJobObject(IntPtr.Zero, null); var info = new JOBOBJECT_BASIC_LIMIT_INFORMATION { LimitFlags = 0x2000 }; var extendedInfo = new JOBOBJECT_EXTENDED_LIMIT_INFORMATION { BasicLimitInformation = info }; int length = Marshal.SizeOf(typeof(JOBOBJECT_EXTENDED_LIMIT_INFORMATION)); IntPtr extendedInfoPtr = Marshal.AllocHGlobal(length); Marshal.StructureToPtr(extendedInfo, extendedInfoPtr, false); if (!SetInformationJobObject(handle, JobObjectInfoType.ExtendedLimitInformation, extendedInfoPtr, (uint)length)) throw new Exception(string.Format("Unable to set information. Error: {0}", Marshal.GetLastWin32Error())); } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } private void Dispose(bool disposing) { if (disposed) return; if (disposing) { } Close(); disposed = true; } public void Close() { CloseHandle(handle); handle = IntPtr.Zero; } public bool AddProcess(IntPtr processHandle) { return AssignProcessToJobObject(handle, processHandle); } public bool AddProcess(int processId) { return AddProcess(Process.GetProcessById(processId).Handle); } } #region Helper classes [StructLayout(LayoutKind.Sequential)] struct IO_COUNTERS { public UInt64 ReadOperationCount; public UInt64 WriteOperationCount; public UInt64 OtherOperationCount; public UInt64 ReadTransferCount; public UInt64 WriteTransferCount; public UInt64 OtherTransferCount; } [StructLayout(LayoutKind.Sequential)] struct JOBOBJECT_BASIC_LIMIT_INFORMATION { public Int64 PerProcessUserTimeLimit; public Int64 PerJobUserTimeLimit; public UInt32 LimitFlags; public UIntPtr MinimumWorkingSetSize; public UIntPtr MaximumWorkingSetSize; public UInt32 ActiveProcessLimit; public UIntPtr Affinity; public UInt32 PriorityClass; public UInt32 SchedulingClass; } [StructLayout(LayoutKind.Sequential)] public struct SECURITY_ATTRIBUTES { public UInt32 nLength; public IntPtr lpSecurityDescriptor; public Int32 bInheritHandle; } [StructLayout(LayoutKind.Sequential)] struct JOBOBJECT_EXTENDED_LIMIT_INFORMATION { public JOBOBJECT_BASIC_LIMIT_INFORMATION BasicLimitInformation; public IO_COUNTERS IoInfo; public UIntPtr ProcessMemoryLimit; public UIntPtr JobMemoryLimit; public UIntPtr PeakProcessMemoryUsed; public UIntPtr PeakJobMemoryUsed; } public enum JobObjectInfoType { AssociateCompletionPortInformation = 7, BasicLimitInformation = 2, BasicUIRestrictions = 4, EndOfJobTimeInformation = 6, ExtendedLimitInformation = 9, SecurityLimitInformation = 5, GroupInformation = 11 } #endregion }
To use the Job object, you’ll use the following code where I’ve passed the same hWnd as used with the WM_CLOSE method:
Job job = new Job(); uint pid = 0; GetWindowThreadProcessId(new IntPtr(hWnd), out pid); job.AddProcess(Process.GetProcessById((int)pid).Handle);
I hope you found this article useful, I certainly learned a lot whilst writing it and I hope you learn as much from it by reading it!
Thank you for reading. Until next time, keep coding!
Available downloads:
This sample Excel add-in was developed using Add-in Express for Office and .net:
55 Comments
learn a lot!
really works, 3ks!
Thanks! This helped me to resolve an issue that’s been plaguing me for DAYS.
Awesome !! Very good explanation and I learned new things. Struggled many hours with the Excel process not going away before finding this very helpful explanation. And great examples that helped explain it. Thanks !
Hi Rick,
I have the same issue with Excel processes but with VB.Net 2010 and now 2013 … I’m happy to read that your problem is solved.. could you tell me which solution you used?
Thank you?
Hi Chris,
I would suggest that you focus on #1, #2 and #3 sections of this post. I am pretty sure they will help you to solve the issue with the frozen excel.exe process.
First of all thanks for all the valuable information that you present here at this blog. I have developed an Excel 2013 Add In using Visual Studio 2013 (VB.NET). The Add In is published by VS as a .VSTO Add In. I have the following questions:
1) I have read that the .dll.deploy file can be easily read by some programs and the source code can be re-generated by specialized software. My question, is there a function / program that allow me to release my .VSTO Add In in an obscured format, in such manner that the original code would not be compromised?
2) After installing .VSTO Add In, I have noticed that Excel doesn’t access the VSTO installation folder at all , so the question, where does my installed Add In reside after I have installed it in a Windows machine?
Thank you. I wish you all the best.
Hi Elin,
I’m very happy you find the information on our blog useful!
As for your questions, please see below:
1) To protect your code you could obfuscate it. There is a very detailed discussion about this on StackOverflow(https://stackoverflow.com/questions/2525/net-obfuscation-tools-strategy). Honestly, in 15 year I’ve never had someone reverse engineer my code, so you must make sure if the trouble to obfuscate outweighs the risk.
2) To get the install folder of your add-in by following the advice in this blog post https://robindotnet.wordpress.com/2010/07/11/how-do-i-programmatically-find-the-deployed-files-for-a-vsto-add-in/
Hope this helps!
Process.Kill helped me to clear my issue. After Formatting Excel and saving I have to Open the excel, which created 2 Excel processes.
Thank you..
It helps me a lot. Thanks.
Now I understand why we need to call the GC.Collect and GC.WaitForPendingFinalizers twice
‘1 dot good, 2 dots bad’
Wow this is the simpliest an most conclusive explanation for this issue.
Thanks a lot.
I was hopeful that this page would provide the answer. It offers the best explanation of what’s going on under the hood that I have run across. Unfortunately my attempt to apply the information does not work.
IN my case I am opening an Excel Workbook that already exists in order to just read some data from it – which I can do quite happily. But can I get rid of that EXCEL instance in the Task manager? Nope. And each time my app calls the function to read an Excel file, I get another of these process instances.
Hi James,
If you only want to read some data from an Excel file, maybe consider using a library such as EPPlus. It does not require Excel to be on the machine, thus It will not create an Excel instance.
The project site is available at https://epplus.codeplex.com/. A nuget package is also available at https://www.nuget.org/packages/EPPlus/
Hope this helps! Good luck!
Thanks, Pieter !
It took me months to learn all these things… glad to see the compilation.
Some extra ideas :
Also use bool IsCom = obj.GetType().IsCOMObject; to make sure you release a COM object and not a different thing.
I’ve also added a balance counter to my code to make sure all COM allocations are released.
Now my project worked perfectly… in my case it uses Word and PowerPoint.
But I’ve recently installed Visual Studio 2015 and .NET 4.6… same code, all and POWERPNT.EXE continues to run. Any idea on possible changes ?
Thanks,
jc
Hi JC,
There’s no problems we’re currently aware of. Maybe have a look at add-ins that are loaded into Powerpoint, it is possible that they can be the culprits rather than your add-in.
Let us know if you get it working. Good Luck!
Thanks for the post
I belive that solution should come from the instant object created .
Regards
thanks for the post. Would replacing try…finally with a using clause be acceptable?
Hi Pwl,
From MSDN, using Statement:
===
The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler.
===
But the Office Object Model interfaces do not implement the IDisposable interface and therefore the using clause will not be able to dispose or release such interfaces.
This really saved my a$$! Unreleased Com objects were causing all sorts of strange behavior in Outlook as well. Thank you.
Thanks very much for the posting. The ‘1 dot good, 2 dots bad’ resolved my issue. Very grateful.
Well Done!
Super good explanation.
Thank u for making the coder planet a better space
Thanks for this post! Solved my EXACT problem in a print-receipt routine. Great article.
I have a question.
Just as foreach is not recommended with COM object collections,
should the following be also avoided:
return doc.ContentControls.Cast().Where(c => c.Tag == tagValue);
where doc is the Microsoft.Office.Tools.Word.Document
Hello Faisal,
Yes, it should be avoided. First off, it creates a COM object by calling doc.ContentControls; that COM object is left unreleased. Second, to scan the collection the code behind the .Cast().Where() construct may create a enumerator. Finally, the enumerator retrieves every content control in the collection to check its Tag; retrieving a COM collection element creates a COM object and you need to release it (unless your code returns it to the caller).
I am trying to read from an excel file using OLEDB. This excel file is linked to other files including MS Access database. When I am trying to read the data, I am getting the following exception at times
The Microsoft Office Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.
The above error is thrown, when trying to open the OLEDB connection.
Could you please let me know how to get this resolved?
Hello Nair,
No, we can’t help you with this as the issue doesn’t relate to the COM object model of any Office application.
My only recommendation is: make sure you don’t have extra EXCEL.EXE processes run; see the Task Manager window. Also, you may be influenced by Excel started in the background if you navigate to an Excel file using Windows Explorer *and* file preview is enabled.
Thanks Andrei. Could you please let me know how we can ensure that there is no extra Excel.exe process? I would need to do this from code. Appreciate if you could share some code snippet.
Hello Nair,
This isn’t something that you can control. Consider some Excel extension that breaks some rules and Excel hangs in the memory due to this. There’s no way for you to control such a scenario.
Thanks Andrei. Is there anyway by which I can break the external links in an excel file so that it’s not getting connected with external datasources.
Hello Nair,
Record a VBA macro while performing these actions in the Excel UI, study the classes/members involved int he process and use them in your code.
Thanks Andrei
I never encountered the problem that application doesn’t exit because of unreleased COM references. The only way I experienced it – is when I accidentally call into office object model from a non-UI thread (i.e. background thread). MSDN (https://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396) says that “Therefore, use the ReleaseComObject only if it is absolutely required. […]”.
Hello Vladimir,
All Office applications were written well before .NET was designed. Those times were times of COM and the very first rule of COM is “Release every COM object that you create”.
In Office 2007 and 2010 Microsoft changed the applications to let them close even if there are COM references unreleased. Note that failing to release COM objects in Office 2000-2003 causes the host application to hang in processes.
Even after this step, in many, many situations, you need to release COM objects in order to bypass an issue. The areas below are most known:
– at https://msdn.microsoft.com/en-us/VBA/outlook-vba/articles/appointmentitem-object-outlook Microsoft recommends releasing COM object when dealing with recurring appointments and Exception objects
– in Outlook programming practice there are *many* indications that releasing COM objects is required when dealing with COM objects representing individual attachments, user properties, recipients, etc. as well as collections of these objects; you can google for “Outlook item leak”
– we saw many problems with COM add-ins if they do not release COM objects when the host application is started programmatically
In fact, you cannot find an official answer to the question: Is it safe to leave this or that COM object unreleased. Meanwhile, the implementation of the COM server is what defines the reaction to leaving a COM object unreleased and it would be very simple to declare this or that Office application (or COM object) safe *if it is safe*. They do not do this.
In this situation, we think that releasing COM objects is exactly what the host application expects from the developer. And our practice seemingly confirms this idea.
As to threads, we don’t recommend using any Office object model from a background thread; you should only make calls into the object model from the main thread of your add-in.
Pieter, you’re my hero for this post.
I see this advice often but this is treating the symptom and not the root of the problem. The issue with having office applications hanging – having a process exist without an open window – is not about “releasing” COM objects but instead it’s about not accessing them correctly in the first place.
Office automation servers are STA servers. They have to be accessed from an STA thread WITH a message pump and that message pump must pump COM messages. Provided you do that then the .NET proxy will handle releasing the COM instances properly – that is after all what it’s designed to do.
Thank you, Michael.
I’ve created a simple example. This is an Outlook add-in showing two custom Ribbon buttons in an Outlook Inspector window. If you click the first button, the add-in obtains the MailItem object (this is a COM object) representing the email opened in the inspector window and sets MailItem.Subject=”qqqqq”. The second button releases the COM object.
To test how the add-in works, turn off all COM add-ins and the Reading Pane (this is a precaution). Now double-click a received email item; this opens the email in an inspector window. Click the first button and notice the modified subject of the email in the inspector window. Close the inspector using the red cross in the top-right corner of the window, click NO when Outlook asks you “Want to save your changes to this message” – this closes the inspector, double-click the same message once again to open a new inspector. Notice that the subject is still set to “qqqqq”. Close the inspector, choose NO, open the message again: the subject is still set to “qqqqq”. Now click the second Ribbon button to release the COM object, close the inspector, choose NO, open the message again: you see the original subject of the email.
I assume this issue cannot be solved in the way other than calling Marshal.ReleaseComObject(item). I have an impression you think otherwise. If this is so, how should I modify the code below?
===
object item = null;
private void adxRibbonButton1_OnClick(object sender, IRibbonControl control, bool pressed)
{
object context = control.Context;
Outlook.Inspector inspector = context as Outlook.Inspector;
if (inspector != null)
{
item = inspector.CurrentItem;
Marshal.ReleaseComObject(inspector);
if (item is Outlook.MailItem)
{
(item as Outlook.MailItem).Subject = “qqqq”;
MessageBox.Show(“Item modified”);
}
else
MessageBox.Show(“not a MailItem”);
}
else
MessageBox.Show(“Something goes wrong”);
}
private void adxRibbonButton2_OnClick(object sender, IRibbonControl control, bool pressed)
{
if (item != null)
{
Marshal.ReleaseComObject(item); item = null;
MessageBox.Show(“Item released”);
}
else
MessageBox.Show(“item is null!”);
}
===
Still had Excel.exe background processes stacking up in my code. Here is what I added to the FINALLY section of my Try/Catch statement and it works. Other methods of killing processes was closing open workbooks not involved in the function of my code block.
Process[] excelProcs = Process.GetProcessesByName(“EXCEL”);
foreach (Process proc in excelProcs)
{
if(proc.MainWindowTitle == “”)
{
proc.Kill();
}
}
Hello Timothy,
We can’t recommend doing this. This isn’t the behavior the host application would expect (and tolerate) from an add-in. You should comment out blocks of code in order to locate the block (line/call) that produces the issue.
Thanks Pieter, I had a call to the garage collector, once, twice is the way forward!
It works! Thank you!
It worked. This saved my time :) Thanks a lot.
Awesome!! thank you!!
No! No! No! No! No! No! No! No!
No! No! No! No! No! No! No! No!
No!!!!
The information on this web page is MISLEADING and based on WRONG assumptions!
The .NET Framework Garbage Collector does an excellent job in cleaning resources.
Do not use Marshal.ReleaseComObject or Marshal.FinalReleaseComObject unless there are very special reasons for it!
See the updated information here:
https://stackoverflow.com/questions/15728676/proper-way-of-releasing-com-objects/38111137#38111137
Especially read this:
https://stackoverflow.com/questions/17130382/understanding-garbage-collection-in-net/17131389#17131389
Note that he warns about the effects of a Debugger, which can confuses developers.
Read the section under “POST MORTEM UPDATE:” here (also contains a reference to the previous hyperlink):
https://stackoverflow.com/questions/158706/how-do-i-properly-clean-up-excel-interop-objects/158752#158752
An example of misinformation being corrected years later.
I am placing this reply here because I have been a victim of the misinformation.
I have been using Marshal.ReleaseComObject for years, also never using more than 1 dot, etc.
Spending lots of time on writing code that is totally unnecessary.
Do not do it that way!
Hello Eduard,
Thank you for your feedback. I started writing a response to you and ended up with an article. You can find it here: https://www.add-in-express.com/creating-addins-blog/releasing-com-objects-garbage-collector-marshal-relseasecomobject/.
Hello Andrei,
Thank you for this wonderful article. Please I am new to developing in the .Net environment. I am currently developing a word vsto add-in and I hardly see any article online about releasing COM objects in word. The emphasis has been on excel application. I am wondering why it is so. Is releasing COM objects in word programming not as important?
Thank you, Patric.
I think 1) there are more Excel developers than Word developers and 2) Word is more tolerant to unreleased COM objects.
Still Word is vast and there are several areas in the object model where non-released COM objects *may* create an issue. I don’t know what exact areas that might be but I’d suspect all new additions to the object model: charts (the object model suggests the charts are assimilated from Excel), all Office-wide things, equations and OMath* objects, and XML* objects.
But this is what I suppose while the reality may differ significantly. I believe their architects foresaw a lot of things and leaving some objects non-released should be among them. Still, these are only “should”, “believe”, and “suppose”: we can’t know these details. What we know 100% is: Word is ready to your releasing COM objects as soon as you are done with them.
My point is: there are nooks in the object model where doing this is the only reaction Word (and all other Office applications) accepts from you. Since we can’t know where these nooks are we should release every COM object we create in our code.
Okay Andrei,
Thank you for the reply. I quite understand your points.
Thanks, the 1 dot rule is excellent
This was very helpfull. Process.Kill has completed what i wanted to do. Thank you
I’m using Visual Studio Pro 2019 16.11.15. I’ve used same/similar code when building in earlier versions of Visual Studio but for some reason when I work in a new class file it seems to close COM object and then it re-opens it, why is that?
string filePath = (string)Dts.Variables[“SourceFile”].Value;
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Excel.Workbook xlWorkBook;
Excel.Sheets xlSheets;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
xlApp.Visible = false;
xlWorkBook = xlWorkBooks.Open(filePath);
//Perform actions in excel
xlSheets = xlWorkBook.Sheets;
xlWorkSheet = xlWorkBook.Worksheets[“SpecificWorksheetName”];
xlRange = xlWorkSheet.Range[“A1”];
xlRange.Replace(“=”, “=”);
xlWorkBook.Close(true);
xlApp.Quit();
//Release COM Objects
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRange);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheets);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
xlRange = null;
xlWorkSheet = null;
xlSheets = null;
xlWorkBook = null;
xlWorkBooks = null;
xlApp = null;
Learned a valuable lesson after several hours troubleshooting, trying many different ways of other peoples solutions online to no success. Having used xlApp in my case allowed me access to many of the interop functions without having to “expand the tree so much” making more variables than necessary. Accessing Range in order to use Replace() was the main thing required for me to perform an action. I can sleep well tonight lol sighs.
Although the exact answer as to why it didn’t work in my earlier attempt (post June 30, 2022 at 4:03 pm) puzzles me, I’m too exhausted at this point. This was the end result of the code that worked.
public void Main()
{
string filePath = (string)Dts.Variables[“SourceFile”].Value;
//Open excel application
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Excel.Workbook xlWorkBook;
Excel.Range xlRange;
xlApp.Visible = false;
//Open workbook
xlWorkBook = xlWorkBooks.Open(filePath);
//Perform actions in excel
xlRange = xlApp.Range[“A1”];
xlRange.Replace(“=”, “=”); //If workbook has existing cells oddly enough it doesn’t evaluate formula unless I clicked inside it after opening file and pressing enter. Naturally I didn’t want user to have to do that and making it into a macro file wasn’t an option. By simply replacing the “=” with itself it fixed the issue and user doesn’t have to worry about manually “enabling” excel formula. Hope this helps somebody out there.
xlWorkBook.Close(true); //true = save workbook before closing.
xlApp.Quit();
////Release COM Objects
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRange);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
xlRange = null;
xlWorkBook = null;
xlWorkBooks = null;
xlApp = null;
}
Hello P J,
You are looking for https://www.add-in-express.com/creating-addins-blog/why-doesnt-excel-quit/. Also, in the context of Office add-ins, we recommend to never use Marshal.FinalReleaseComObject().
Andrei,
Thank you very much for the guidance, I do have one follow up question I can’t seem to find answers elsewhere.
The following code used to work fine last year but somehow it’s no longer closing all COM objects.
I’m not sure why this worked before, if it has the two dots at these lines “xlWorkBookDefault”, “xlNoChange”. But these are not interfaces, they are enumerators and assigning these to variables hasn’t worked for me no matter what I’ve tried it always wants me to use two dots.
The other issue is that if I were to type this “MyBook.SaveAs(xlsxOutput);”, it saves the file as .xlsx but it’s then corrupted. The string xlsxOutput variable contains the “.xlsx” extension within SSIS variable window.
Yeah, not sure what else to do even Stack overflow rate limited me because I was accessing there website to many times trying to find answers. Maybe you can point me to a resource or perhaps I’m creating RCW and can’t access them. Using C# 2019.
——————————-
public void Main()
{
string flatSource = Dts.Variables[“FormatFlatFileSource”].Value.ToString();
string xlsxOutput = Dts.Variables[“FormatXLSXOutput”].Value.ToString();
Excel.Application APexcel = new Excel.Application();
Excel.Workbooks xlWorkBooks = APexcel.Workbooks;
Excel.Workbook MyBook;
/*I’ve no clue how to work around these next two lines since they are
enumerators, not interfaces.*/
Excel.XlFileFormat xlWorkBookDefault = Excel.XlFileFormat.xlWorkbookDefault;
Excel.XlSaveAsAccessMode xlNoChange = Excel.XlSaveAsAccessMode.xlNoChange;
APexcel.Visible = false;
object fieldInfo = new int[43, 2]
{ {0, 2}, {8, 2}, {38, 2}, {68, 2}, {88, 2}, {108, 2},
{110, 2}, {115, 2}, {125, 2}, {128, 5}, {136, 5}, {144, 2},
{147, 5}, {155, 5}, {163, 2}, {166, 5}, {174, 5}, {182, 2},
{185, 5}, {193, 5}, {201, 2}, {204, 5}, {212, 5}, {220, 2},
{223, 5}, {231, 5}, {239, 9}, {363, 2}, {376, 2}, {387, 2},
{388, 2}, {393, 2}, {423, 2}, {426, 2}, {436, 2}, {445, 2},
{480, 2}, {510, 2}, {530, 2}, {550, 2}, {552, 2}, {557, 2},
{567, 1}
};
xlWorkBooks.OpenText(Filename: flatSource,
Origin: 437,
StartRow: 1,
FieldInfo: fieldInfo,
TrailingMinusNumbers: true);
APexcel.Cells[1, 1] = “ColumnHeader1”;
APexcel.Cells[1, 2] = “ColumnHeader2”;
APexcel.Cells[1, 3] = “ColumnHeader3”;
APexcel.Cells[1, 4] = “ColumnHeader4”;
APexcel.Cells[1, 5] = “ColumnHeader5”;
APexcel.Cells[1, 6] = “ColumnHeader6”;
APexcel.Cells[1, 7] = “ColumnHeader7”;
APexcel.Cells[1, 8] = “ColumnHeader8”;
APexcel.Cells[1, 9] = “ColumnHeader9”;
APexcel.Cells[1, 10] = “ColumnHeader10”;
APexcel.Cells[1, 11] = “ColumnHeader11”;
APexcel.Cells[1, 12] = “ColumnHeader12”;
APexcel.Cells[1, 13] = “ColumnHeader13”;
APexcel.Cells[1, 14] = “ColumnHeader14”;
APexcel.Cells[1, 15] = “ColumnHeader15”;
APexcel.Cells[1, 16] = “ColumnHeader16”;
APexcel.Cells[1, 17] = “ColumnHeader17”;
APexcel.Cells[1, 18] = “ColumnHeader18”;
APexcel.Cells[1, 19] = “ColumnHeader19”;
APexcel.Cells[1, 20] = “ColumnHeader20”;
APexcel.Cells[1, 21] = “ColumnHeader21”;
APexcel.Cells[1, 22] = “ColumnHeader22”;
APexcel.Cells[1, 23] = “ColumnHeader23”;
APexcel.Cells[1, 24] = “ColumnHeader24”;
APexcel.Cells[1, 25] = “ColumnHeader25”;
APexcel.Cells[1, 26] = “ColumnHeader26”;
APexcel.Cells[1, 27] = “ColumnHeader27”;
APexcel.Cells[1, 28] = “ColumnHeader28”;
APexcel.Cells[1, 29] = “ColumnHeader29”;
APexcel.Cells[1, 30] = “ColumnHeader30”;
APexcel.Cells[1, 31] = “ColumnHeader31”;
APexcel.Cells[1, 32] = “ColumnHeader32”;
APexcel.Cells[1, 33] = “ColumnHeader33”;
APexcel.Cells[1, 34] = “ColumnHeader34”;
APexcel.Cells[1, 35] = “ColumnHeader35”;
APexcel.Cells[1, 36] = “ColumnHeader36”;
APexcel.Cells[1, 37] = “ColumnHeader37”;
APexcel.Cells[1, 38] = “ColumnHeader38”;
APexcel.Cells[1, 39] = “ColumnHeader39”;
APexcel.Cells[1, 40] = “ColumnHeader40”;
APexcel.Cells[1, 41] = “ColumnHeader41”;
MyBook = APexcel.ActiveWorkbook;
MyBook.SaveAs(xlsxOutput,
xlWorkBookDefault ,
Type.Missing, // Password, no
Type.Missing, // ResPassword, no
Type.Missing, // ReadOnly, no
Type.Missing, // CreateBackup, no
xlNoChange ,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
MyBook.Close();
APexcel.Quit(); /*Observation: the moment this line runs it closes Excel
Program however the EXCEL.EXE comes up again in Task Manager*/
System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(APexcel);
MyBook = null;
xlWorkBooks = null;
APexcel = null;
}
Hello PJ,
> But these are not interfaces
Ignore non-interfaces. The “two dots” stuff is about interfaces only.
The issue is in “APexcel.Cells[i, j]”; comment these lines to check if this is so. If yes, you’ll need to experiment with two possible candidates:
– APexcel.Cells returns a Range object; try to set it to a variable and release it after use.
– I suppose APexcel.Cells[i, j] also returns a Range object; try to set it to a variable and release it after use.
Andrei,
Thank you once more for your help. I’m on day 7 of troubleshooting, though today was successful *knock on wood*. Learned that using “APexcel.Cells[] the Cells[] portion can create a COM that doesn’t release at least that’s what my observation led me to understand after running line by line/comparing and watching task manager. So yes you are right on the money with your idea, that in fact, Cells[i, j]; was the culprit. Same thing for Columns[] that didn’t allow me to release COM when experimenting.
So instead of that, using Range[] worked much better and releasing COMs was no trouble anymore. Then another issue came up I couldn’t re-use the same Excel.Range variable to apply the different column names and I didn’t want to make the code hard to read by creating multiple range variables as I have about 40 fields; ended up going with a string[] array and used a for loop to automatically add the column titles in the correct position. Up to this point COMs are closing as expected.
Hello PJ,
> I couldn’t re-use the same Excel.Range variable to apply the different column names
If you need to reuse a variable containing a COM reference, release the variable before assigning it a new value.