Interop Excel, process not closed because of cell value object

Question

I'am using interop Excel to generate some reports in an asp.net application. The problem ist that the Excel process does not close after I generated the Excel file, even if i close all com objects using System.Runtime.InteropServices.Marshal.ReleaseComObject. The cause of the problem seems to be that I set the value of an Excel range to an object in my code. Here is an example:

If I directly set a string to the value, the process is killed after i call the Quit() command:
MyWorksheet.Cells(2, "B").Value = "Test"
But if i set the Value to an Object the process is still alive after the Quit() command:
dim strTest as string = "Test"
MyWorksheet.Cells(2, "B").Value = strTest

I tried to release these objects with the ReleaseComObject command, but that didn't solve the problem. Is there a way to set the value to an object and then release the object? Or is there a way to pass the object by value instead ob by reference?

Answer 1

Read this thread. With COM Interop, you don't want to leave any of the exposed objects hanging. That thread explains why, and how to work around this.

---

Coding Light - Illuminated Ideas and Algorithms in Software
Coding Light Wiki • LinkedIn • ForumsBrowser

Answer 2

Did you set the application object to Null after calling the quit method. Try calling the Garbage collector with WaitForpendingFinalizers. Not the best approach, but it works for me.

---

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]
blog.ganeshzone.net

Answer 3

I tried to set everything to null, but it only seems to work if I only set one object. If I set two or more objects the process is not killed after the Quit command. Here is an example:

In this Example the process is killed:

Dim strTest01 As String = "Test"
MyWorksheet.Cells(2, "B").Value() = strTest01
strTest01 = Nothing

In this example the process is not killed:

Dim strTest01 As String = "Test"
MyWorksheet.Cells(2, "B").Value() = strTest01
strTest01 = Nothing
Dim strTest02 As String = "Test"
MyWorksheet.Cells(3, "B").Value() = strTest02
strTest02 = Nothing

What am I doing wrong?

Answer 4

Did you read the link I directed you to?

Your trouble is here:

MyWorksheet.Cells(2, "B").Value = strTest01;

Notice you've got two periods in there.

Try this:

var range = MyWorksheet.Cells(2, "B");
range.Value = strTest01;

Marshal.ReleaseComObject(range);
range = null;
Marshal.ReleaseComObject(MyWorksheet);

In other words, you're creating getting the range, but not releasing the range com object.  The only way to release the range com object is to have a handle on the range you need to release.  The only way to do that is to create an intermediate variable to hold your range.

Please read the post I linked to above. It describes this in more detail, and tells you what you are doing wrong. I didn't post it for my health.

---

Coding Light - Illuminated Ideas and Algorithms in Software
Coding Light Wiki • LinkedIn • ForumsBrowser

Answer 5

Thanks for your help, with you code it works a much better. Now only the first Excel process is not getting killed, but the following are all getting killed after the Quit command. But why is the first process not getting killed after the quit command?

Answer 6

Hi,
David gave a helpful link, you may find the answer from his article. if you still have any concern, do you mind posting some code snippet that brings you troubles, it will be useful for further investigation.

Thanks,
Eric

---

Please remember to mark helpful replies as answers and unmark them if they provide no help.

Answer 7

try calling postmessage to quit excel instance.
BTW what version of excel you are using.