By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

excel doesn't close after SaveAs; com object release trouble

P: n/a
Hi,

I'm trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that I
create. The code below runs just fine and excel is closed. But there are
some commented lines:

//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...

// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);

If I uncomment either one of these commands, excel won't close anymore. I
guess these lines create some COM objects that I have to release. My
questions:

1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so excel
can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up the
memory or present a memory leak?

Thank a lot in advance, best regards,
-Hendri Adriaens.

--------- The code ---------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;

public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}

private void button1_Click(object sender, EventArgs e)
{

Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXC EL"))
// {
// proc.Kill();
// }
}

private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseCom Object(theObject) >
0)
{
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}

}
May 23 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Hendri,

A few things to point out. First, your call to releaseComObject is
cycling through a loop to call ReleaseComObject. You do not need to do
this. You should only have to do this once. Second, you are passing the
object by value. Setting the object to null in that routine isn't going to
set the reference passed to it in the theObject parameter null upon return.

Your loop through the worksheets is superfluous, as you are just
creating references that you didn't have in the first place and then
releasing them.

Every time you call the Offset property on the Range instance, it
returns a new Range instance which you need to call ReleaseComObject on. I
don't see that here. Those references are definitely hanging around.

Assuming you aren't holding onto these references anywhere, you might
want to forego calling ReleaseComObject and just call the static Collect
method on the GC class, and then call the static WaitForPendingFinalizers
method right after you call Quit on the application object.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Hendri Adriaens" <sp**********@THISgmail.comwrote in message
news:3d***************************@news1.tudelft.n l...
Hi,

I'm trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that
I create. The code below runs just fine and excel is closed. But there are
some commented lines:

//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...

// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);

If I uncomment either one of these commands, excel won't close anymore. I
guess these lines create some COM objects that I have to release. My
questions:

1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so
excel can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up
the memory or present a memory leak?

Thank a lot in advance, best regards,
-Hendri Adriaens.

--------- The code ---------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;

public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}

private void button1_Click(object sender, EventArgs e)
{

Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXC EL"))
// {
// proc.Kill();
// }
}

private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseCom Object(theObject)
0)
{
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}

}
May 23 '07 #2

P: n/a
Hi Nicholas,
A few things to point out. First, your call to releaseComObject is
cycling through a loop to call ReleaseComObject. You do not need to do
this. You should only have to do this once.
I think you are referring to my releaseComObject method with the while loop.
I read that that is necessary for some objects. Indeed, I couldn't get rid
of xlSeries without this. The other objects work by calling ReleaseComObject
just once, but xlSeries really didn't unfortunately. I read on a website
that in general, we have to loop until the counter is zero. And that indeed
gets rid of xlSeries.

But maybe I am missing your point?
Second, you are passing the object by value. Setting the object to null
in that routine isn't going to set the reference passed to it in the
theObject parameter null upon return.
Ok, but it works as it is now. Excel is closed successfully (if we leave the
trouble lines commented) and specifying ref didn't compile (it complained
about converting Excel.<somthingto object).

Please suggest me the correct code then.
Your loop through the worksheets is superfluous, as you are just
creating references that you didn't have in the first place and then
releasing them.
Uh, that was also necessary to get excel to close. I read that here and just
verified it once again and it's really necessary:
http://www.devcity.net/Articles/239/3/article.aspx
Every time you call the Offset property on the Range instance, it
returns a new Range instance which you need to call ReleaseComObject on.
I don't see that here. Those references are definitely hanging around.
I don't think so. As stated, my initial code works fine. I can only conclude
that I have to disagree, sorry.
Assuming you aren't holding onto these references anywhere, you might
want to forego calling ReleaseComObject and just call the static Collect
method on the GC class, and then call the static WaitForPendingFinalizers
method right after you call Quit on the application object.
Well, that's what I'm doing. Leaving out garbage collection also makes excel
stay open. I tested the code thoroughly and made it as minimal as possible.
Leaving anything out wrt releasing objects or garbage collection makes excel
stay open.

Thanks for your reaction. Best regards,
-Hendri.
May 23 '07 #3

P: n/a
Hendri,
I think you are referring to my releaseComObject method with the while
loop.
I read that that is necessary for some objects. Indeed, I couldn't get rid
of xlSeries without this. The other objects work by calling
ReleaseComObject
just once, but xlSeries really didn't unfortunately. I read on a website
that in general, we have to loop until the counter is zero. And that
indeed
gets rid of xlSeries.
The loop is superfluous. Given the documentation, which says that the
internal count on the runtime callable wrapper is incremented every time an
interface pointer is mapped to it, as well as passing it in and out of
unmanaged code, you know exactly how many times to release your object. You
know the lifetime of your object here, and only have to make the call once.

IMO, the recommendation of the documentation to loop to fully release
the object is not correct, as it promotes the idea that you should not be
aware of the lifetime of your objects, which is not really a good idea when
working with COM. In .NET, if you are not implementing IDisposable, then
the lifetime of your objects is not such a concern, but COM is a different
beast entirely as you are dealing with reference counts and specific
lifetimes.
Ok, but it works as it is now. Excel is closed successfully (if we leave
the
trouble lines commented) and specifying ref didn't compile (it complained
about converting Excel.<somthingto object).
Yes, the releasing part works, but the assignment of the parameter to
null does not work. You aren't doing anything with it here, so it doesn't
matter, but the code does nothing by setting the flag in your
releaseComObject to true. You need to pass the theObject parameter with a
ref modifier in order to have the assignment to null take effect.
Uh, that was also necessary to get excel to close. I read that here and
just
verified it once again and it's really necessary:
http://www.devcity.net/Articles/239/3/article.aspx
The article is wrong as well. It is just creating references that might
not have existed already and releasing them. It's effectively performing a
no op. If the references that have not been released correctly are
workbooks or worksheets, then this will work, but that's not the case for
most apps that interop with excel (you will have references to ranges which
are probably orphaned).

The article is basically proposing that you walk the whole object model
of the workbook, but only goes one level deep, in accessing the worksheets.
It shows a lack of understanding of how COM interop works, in that it is
going to traverse the entire heiarchy in order to track down and release one
reference that it should have been keeping track of in the first place.

Also, the following statement in the article is false:

Since Microsoft Excel is built upon component architecture, more
specifically, using COM+ interfaces

Specifically, the last part about COM+ interfaces. COM+ is a component
services provider which Excel does not take advantage of. Excel uses COM.
I don't think so. As stated, my initial code works fine. I can only
conclude
that I have to disagree, sorry.
With all due respect, if it worked fine, then you wouldn't have the
problem with Excel not shutting down correctly.
Well, that's what I'm doing. Leaving out garbage collection also makes
excel
stay open. I tested the code thoroughly and made it as minimal as
possible.
Leaving anything out wrt releasing objects or garbage collection makes
excel
stay open.

Thanks for your reaction. Best regards,
-Hendri.
I've attached a program file which does what you are trying to do,
correctly releasing all the references without using a general loop to
release references. Running this using Excel 2003, the application does
shut down correctly. The only changes to note are the path to the output
file in the call to SaveAs, as well as the value from the
XlSaveConflictResolution enumeration (I'm guessing you are using Excel
2007).

There were a number of other glaring omissions from your code when I
worked through it, most notably, the failure to call ReleaseComObject when
you reassigned the xlRange variable to another range instance (multiple
times).

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

May 23 '07 #4

P: n/a
Hi Nicholas,
The loop is superfluous. Given the documentation, which says that the
internal count on the runtime callable wrapper is incremented every time
an interface pointer is mapped to it, as well as passing it in and out of
unmanaged code, you know exactly how many times to release your object.
You know the lifetime of your object here, and only have to make the call
once.
Ok, but I don't keep track of the lifetime.
IMO, the recommendation of the documentation to loop to fully release
the object is not correct, as it promotes the idea that you should not be
aware of the lifetime of your objects, which is not really a good idea
when working with COM. In .NET, if you are not implementing IDisposable,
then the lifetime of your objects is not such a concern, but COM is a
different beast entirely as you are dealing with reference counts and
specific lifetimes.
Ok.
Yes, the releasing part works, but the assignment of the parameter to
null does not work. You aren't doing anything with it here, so it doesn't
matter, but the code does nothing by setting the flag in your
releaseComObject to true. You need to pass the theObject parameter with a
ref modifier in order to have the assignment to null take effect.
I tried that, but especially that didn't work.
The article is wrong as well. It is just creating references that
might not have existed already and releasing them. It's effectively
performing a no op. If the references that have not been released
correctly are workbooks or worksheets, then this will work, but that's not
the case for most apps that interop with excel (you will have references
to ranges which are probably orphaned).
Ok.
The article is basically proposing that you walk the whole object model
of the workbook, but only goes one level deep, in accessing the
worksheets. It shows a lack of understanding of how COM interop works, in
that it is going to traverse the entire heiarchy in order to track down
and release one reference that it should have been keeping track of in the
first place.
Ok. I'm not going to defend the article, it's not mine, but I guess the
author just wanted to present a method that releases as much as possible
objects, a general method, workable by copy&paste instead of needing to
understand the entire COM model.
Since Microsoft Excel is built upon component architecture, more
specifically, using COM+ interfaces

Specifically, the last part about COM+ interfaces. COM+ is a component
services provider which Excel does not take advantage of. Excel uses COM.
Ok.
With all due respect, if it worked fine, then you wouldn't have the
problem with Excel not shutting down correctly.
The code presented in the first place was working fine. When uncommenting a
commented line, it gave problems. I wanted to understand why
xlSeries.XValues= ... created another COM object, what it was and how I
could release it.
I've attached a program file which does what you are trying to do,
correctly releasing all the references without using a general loop to
release references. Running this using Excel 2003, the application does
shut down correctly. The only changes to note are the path to the output
file in the call to SaveAs, as well as the value from the
XlSaveConflictResolution enumeration (I'm guessing you are using Excel
2007).
Thanks a lot for your code! It's much appreciated.
I'm using excel 2003. Do I have to change anything then?

Unfortunately, I can't build it. I get the following message and errors:

Message 1 The designer has fixed inconsistent type names in a partial class.
The partial class name 'WindowsApplication1' has been changed to
'WindowsApplication2'. 0 0

Error 2 The type or namespace name 'Interop' does not exist in the namespace
'Microsoft.Office' (are you missing an assembly reference?) D:\Mijn
documenten\CVZ\excelCleanDemo\WindowsApplication1\ Form1.cs 9 24
WindowsApplication1

Error 3 'System.Data.Range' is inaccessible due to its protection level
D:\Mijn documenten\CVZ\excelCleanDemo\WindowsApplication1\ Form1.cs 124 43
WindowsApplication1

I don't know what to do with these. Do you have an idea?
There were a number of other glaring omissions from your code when I
worked through it, most notably, the failure to call ReleaseComObject when
you reassigned the xlRange variable to another range instance (multiple
times).
Ok, thank you very much for the help so far!

Best regards,
-Hendri.
May 23 '07 #5

P: n/a
Message 1 The designer has fixed inconsistent type names in a partial
class. The partial class name 'WindowsApplication1' has been changed to
'WindowsApplication2'. 0 0
Oh, ok, I found it already. The namespace was renamed. Naming it back solved
the issue.

And should I change the value from the XlSaveConflictResolution enumeration
for Excel 2003?

Thanks, best regards,
-Hendri.
May 23 '07 #6

P: n/a
You should be able to pass the value, if you need it, or pass missing (I
believe I used missing).

You comment that you don't keep track of the lifetime. That's something
I am trying to impress on you. COM is a different programming model than
..NET, and if you want to work with that model (even though you are using it
from .NET), you have to play by its rules. You need to keep track of
lifetimes here.

The comment about your releaseComObject implementation was not important
to the issue of handling COM objects, but rather, an error in the code where
you are implying that you would set the reference to null in the routine.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Hendri Adriaens" <sp**********@THISgmail.comwrote in message
news:ee**************************@news1.tudelft.nl ...
>Message 1 The designer has fixed inconsistent type names in a partial
class. The partial class name 'WindowsApplication1' has been changed to
'WindowsApplication2'. 0 0

Oh, ok, I found it already. The namespace was renamed. Naming it back
solved the issue.

And should I change the value from the XlSaveConflictResolution
enumeration for Excel 2003?

Thanks, best regards,
-Hendri.

May 23 '07 #7

P: n/a
And should I change the value from the XlSaveConflictResolution
enumeration for Excel 2003?
Ok, I noticed that I had to put
Excel.XlSaveConflictResolution.xlLocalSessionChang es back because excel
wasn't writing any file to disk. BTW, when inserting
XlSaveConflictResolution.xlLocalSessionChanges, I got all kind of namespace
problems which I had before, so removed the
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

Now it's working fine. It's unfortunate that writing an excel file requires
all this knowledge about Com objects, internal counts, releases and more.
I'll study the code and hope to be able to extend it in a safe way as this
was only an example.

Thanks a lot for your help, best regards,
-Hendri.
May 23 '07 #8

P: n/a
You comment that you don't keep track of the lifetime. That's
something I am trying to impress on you. COM is a different programming
model than .NET, and if you want to work with that model (even though you
are using it from .NET), you have to play by its rules. You need to keep
track of lifetimes here.
Ok, yes, will try to do that. But I never would have guessed it would take
so much effort to write an excel file.

Best regards,
-Hendri.
May 23 '07 #9

P: n/a
Hendri,

I would advise against removing the namespace declarations.

If you are having namespace conflicts with that enumeration, then I
would recommend specifying the full type name of the enumeration, rather
than removing the using statement, and having to use full type declarations
for them all.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Hendri Adriaens" <sp**********@THISgmail.comwrote in message
news:ce***************************@news1.tudelft.n l...
>And should I change the value from the XlSaveConflictResolution
enumeration for Excel 2003?

Ok, I noticed that I had to put
Excel.XlSaveConflictResolution.xlLocalSessionChang es back because excel
wasn't writing any file to disk. BTW, when inserting
XlSaveConflictResolution.xlLocalSessionChanges, I got all kind of
namespace problems which I had before, so removed the
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

Now it's working fine. It's unfortunate that writing an excel file
requires all this knowledge about Com objects, internal counts, releases
and more. I'll study the code and hope to be able to extend it in a safe
way as this was only an example.

Thanks a lot for your help, best regards,
-Hendri.

May 23 '07 #10

P: n/a
Hi Nicholas,
I would advise against removing the namespace declarations.

If you are having namespace conflicts with that enumeration, then I
would recommend specifying the full type name of the enumeration, rather
than removing the using statement, and having to use full type
declarations for them all.
Ok, may I ask why actually? I would say it might cause namespace problems in
the future.

Anyway, when putting things back like you did it, it gives:

Error 1 The type or namespace name 'Interop' does not exist in the namespace
'Microsoft.Office' (are you missing an assembly reference?) D:\Mijn
documenten\CVZ\excelCleanDemo\WindowsApplication1\ Form1.cs 9 24
WindowsApplication1

Error 2 'System.Data.Range' is inaccessible due to its protection level
D:\Mijn documenten\CVZ\excelCleanDemo\WindowsApplication1\ Form1.cs 129 43
WindowsApplication1

Error 2 can be solved by changing

private void SetOffsetRangeValue2(Range range ...

to

private void SetOffsetRangeValue2(Excel.Range range ...

I don't see how to solve error 1. Indeed, I don't have a
Microsoft.Office.Interop.Excel reference, but I didn't need that before
(adding "Excel." in front of objects was enough to make it work) and it
wasn't also automatically added when I added the reference "Microsoft Excel
11.0 Object Library". How can I add this reference?

Thanks, best,
-Hendri.
May 23 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.