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

excel process not terminating properly

P: n/a
hello all you gurus. I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end. So I feel
confident the problem occurrs here. It appears another reference is
created that needs to be closed. Can anyone tell me how to do
this? :)
Thank you

Excel.Workbook workbook =
(Excel.Workbook)excelapplication.ActiveWorkbook;
Excel.Sheets sheet = workbook.Worksheets;

// problem here
int k = sheet.Count;

System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
sheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject(workbook);
workbook = null;

Apr 18 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Try adding the following just BEFORE your call ReleaseCOMObject on them:

sheet = null;
workbook = null;

<ch**********@hotmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
hello all you gurus. I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end. So I feel
confident the problem occurrs here. It appears another reference is
created that needs to be closed. Can anyone tell me how to do
this? :)
Thank you

Excel.Workbook workbook =
(Excel.Workbook)excelapplication.ActiveWorkbook;
Excel.Sheets sheet = workbook.Worksheets;

// problem here
int k = sheet.Count;

System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
sheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject(workbook);
workbook = null;

Apr 18 '07 #2

P: n/a
"Scott M." <s-***@nospam.nospamschrieb:
Try adding the following just BEFORE your call ReleaseCOMObject on them:

sheet = null;
workbook = null;
Bad idea, because 'ReleaseComObject' won't release the objects if a null
reference is passed to it.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 18 '07 #3

P: n/a
<ch**********@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.
PRB: Office Application Does Not Quit After Automation from Visual Studio
..NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Apr 18 '07 #4

P: n/a
Your link shows (in a more elaborate way) how to do what the OP is already
doing (RleaseCOMObject and set to null). Is there a particular part of the
article that you suggest?
"Herfried K. Wagner [MVP]" <hi***************@gmx.atwrote in message
news:uF**************@TK2MSFTNGP06.phx.gbl...
<ch**********@hotmail.comschrieb:
>I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 18 '07 #5

P: n/a
"Scott M." <s-***@nospam.nospamschrieb:
Your link shows (in a more elaborate way) how to do what the OP is already
doing (RleaseCOMObject and set to null). Is there a particular part of
the article that you suggest?
I suggest the "more elaborate way".

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 18 '07 #6

P: n/a
On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
<chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
thank you for your resposnse and I have to admit I am somewhat
confused (I guess that's obvious). I read the article and maybe I
missed the obvious, but I do use GC.Collect() and
GC.WaitForPendingFinalizers() and excelapplication.quit(). And I
think I understand that I must explicitly reference any object that is
implicitly created. So, I think (I am probably wrong) int k =
sheet.Count; creates an implicit reference that I must explicitly
reference so I can remove the reference. Is this correct? If so , how
do I do that? If I am wrong, please correct me. Everything works
fine until this statement is added. Thank you

Apr 18 '07 #7

P: n/a
On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
<chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
thank you for your resposnse and I have to admit I am somewhat
confused (I guess that's obvious). I read the article and maybe I
missed the obvious, but I do use GC.Collect() and
GC.WaitForPendingFinalizers() and excelapplication.quit(). And I
think I understand that I must explicitly reference any object that is
implicitly created. So, I think (I am probably wrong) int k =
sheet.Count; creates an implicit reference that I must explicitly
reference so I can remove the reference. Is this correct? If so , how
do I do that? If I am wrong, please correct me. Everything works
fine until this statement is added. Thank you

Apr 18 '07 #8

P: n/a
<ch**********@hotmail.comwrote in message
news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
><chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

thank you for your resposnse and I have to admit I am somewhat
confused (I guess that's obvious). I read the article and maybe I
missed the obvious, but I do use GC.Collect() and
GC.WaitForPendingFinalizers() and excelapplication.quit(). And I
think I understand that I must explicitly reference any object that is
implicitly created. So, I think (I am probably wrong) int k =
sheet.Count; creates an implicit reference that I must explicitly
reference so I can remove the reference. Is this correct? If so , how
do I do that? If I am wrong, please correct me. Everything works
fine until this statement is added. Thank you

No, Count does not create an reference to a COM object, sheet holds a reference to the COM
interface.
But as long as you don't post a *complete* sample, that illustrates the issue, you won't get
any sensible answers.

A complete sample looks something like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Reflection;

using Exl = Microsoft.Office.Interop.Excel;
namespace OffExc
{
class Program
{
[STAThread]
static void Main(string[] args)
{
System.Threading.Thread.CurrentThread.CurrentCultu re = new CultureInfo( "en-US",
false );
Exl.Application exApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Reference 1
Exl.Workbook wb = exApp.Workbooks.Add(Missing.Value); // Reference 2
exApp.Visible = true;
// Keep Excel visible for a while..
System.Threading.Thread.Sleep(3000);
Exl.Sheets sheet = wb.Worksheets; // // Reference 3
int k = sheet.Count;
// Quit
exApp.Quit();
// Release the three COM references...
System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(exApp);
GC.Collect();
// Let the finalizer run, this one will delete the unmanaged COM wrappers,
// if for one or another reason, the finalizer cannot run to completion,
// chances are that the Excel process won't get removed !!!!!!!!
GC.WaitForPendingFinalizers();
// Excel should be gone by now... keep the console processrunning for a while
System.Threading.Thread.Sleep(30000);
}
}
}

And above code works as expected on my box.

Willy.

Apr 18 '07 #9

P: n/a
this is a scam

the problem is using DAO. screw DAO and you won't have this problem

ADO works like a charm


On Apr 18, 12:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
<chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 18 '07 #10

P: n/a
On Apr 18, 5:38 pm, "Willy Denoyette [MVP]"
<willy.denoye...@telenet.bewrote:
<chuckie_9...@hotmail.comwrote in message

news:11**********************@b58g2000hsg.googlegr oups.com...


On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
<chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.
PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
thank you for your resposnse and I have to admit I am somewhat
confused (I guess that's obvious). I read the article and maybe I
missed the obvious, but I do use GC.Collect() and
GC.WaitForPendingFinalizers() and excelapplication.quit(). And I
think I understand that I must explicitly reference any object that is
implicitly created. So, I think (I am probably wrong) int k =
sheet.Count; creates an implicit reference that I must explicitly
reference so I can remove the reference. Is this correct? If so , how
do I do that? If I am wrong, please correct me. Everything works
fine until this statement is added. Thank you

No, Count does not create an reference to a COM object, sheet holds a reference to the COM
interface.
But as long as you don't post a *complete* sample, that illustrates the issue, you won't get
any sensible answers.

A complete sample looks something like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Reflection;

using Exl = Microsoft.Office.Interop.Excel;
namespace OffExc
{
class Program
{
[STAThread]
static void Main(string[] args)
{
System.Threading.Thread.CurrentThread.CurrentCultu re = new CultureInfo( "en-US",
false );
Exl.Application exApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Reference 1
Exl.Workbook wb = exApp.Workbooks.Add(Missing.Value); // Reference 2
exApp.Visible = true;
// Keep Excel visible for a while..
System.Threading.Thread.Sleep(3000);
Exl.Sheets sheet = wb.Worksheets; // // Reference 3
int k = sheet.Count;
// Quit
exApp.Quit();
// Release the three COM references...
System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(exApp);
GC.Collect();
// Let the finalizer run, this one will delete the unmanaged COM wrappers,
// if for one or another reason, the finalizer cannot run to completion,
// chances are that the Excel process won't get removed !!!!!!!!
GC.WaitForPendingFinalizers();
// Excel should be gone by now... keep the console processrunning for a while
System.Threading.Thread.Sleep(30000);
}
}

}

And above code works as expected on my box.

Willy.- Hide quoted text -

- Show quoted text -
thank you gentlmen for your help. the following seems to be working
correctly. of course I have alot more to do but this will get me
started. Is there a "better" way to save the workbook (ie, use
wb.saveas) and is there a better way way to enumerate and delete
unwanted sheets? Also, I realize error checking needs to be
implemented. Thanks again for your assistance.

using System;
using System.Reflection;
using System.Threading;
using System.Globalization;
using Exl = Microsoft.Office.Interop.Excel;

namespace mynamespace
{
class myexcel
{
public void test()
{
System.Threading.Thread.CurrentThread.CurrentCultu re = new
CultureInfo("en-US", false);
Exl.Application exApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
// Reference 1
Exl.Workbook wb = exApp.Workbooks.Add(Missing.Value); //
Reference 2
exApp.Visible = true;
Exl.Sheets sheet = wb.Worksheets; // Reference 3
// set active sheet
Exl.Worksheet activesheet = (Exl.Worksheet)wb.ActiveSheet;
activesheet.Name = "testsheet";
// write to cell A1
Exl.Range range;
range = activesheet.get_Range("A1", Missing.Value);
range.Value2 = "test input";

System.Runtime.InteropServices.Marshal.ReleaseComO bject(range);
// disable alerts so I'm not prompted when a worksheet is
deleted
exApp.DisplayAlerts = false;
// delete a sheet
for (int i = 1; i <= sheet.Count; i++)
{
// would like to delete
Exl.Worksheet ob = (Exl.Worksheet)exApp.Worksheets[i];
if (ob.Name.ToLower().Substring(0, 5).Equals("sheet"))
ob.Delete();
ReleaseComObject(ob);
ob = null;
}
// save the workbook
exApp.ActiveWorkbook.Close(true, @"c:\testxls.xls",
Missing.Value);
// Quit
exApp.Quit();
// Release the COM references...
ReleaseComObject(activesheet);
ReleaseComObject(sheet);
ReleaseComObject(wb);
ReleaseComObject(exApp);
GC.Collect();
// Let the finalizer run, this one will delete the unmanaged
COM wrappers,
// if for one or another reason, the finalizer cannot run to
completion,
// chances are that the Excel process won't get
removed !!!!!!!!
GC.WaitForPendingFinalizers();
// Excel should be gone by now... keep the console
processrunning for a while
System.Threading.Thread.Sleep(3000);
}
public void ReleaseComObject(Object reference)
{
try
{
while
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(reference) >
-1) ;
}
catch (Exception ex)
{
// handle exception
}
finally
{
reference = null;
}
}
}
}


Apr 19 '07 #11

P: n/a
By more elaborate, I just meant that they've broken the release of the
object into a separate function. It seems that the OP is doing the same
thing in his code, just not in a separate function. What, exactly, is the
article showing that the OP isn't doing?
"Herfried K. Wagner [MVP]" <hi***************@gmx.atwrote in message
news:ON**************@TK2MSFTNGP06.phx.gbl...
"Scott M." <s-***@nospam.nospamschrieb:
>Your link shows (in a more elaborate way) how to do what the OP is
already doing (RleaseCOMObject and set to null). Is there a particular
part of the article that you suggest?

I suggest the "more elaborate way".

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 19 '07 #12

P: n/a
"Scott M." <s-***@nospam.nospamschrieb:
By more elaborate, I just meant that they've broken the release of the
object into a separate function. It seems that the OP is doing the same
thing in his code, just not in a separate function. What, exactly, is the
article showing that the OP isn't doing?
Take a look at the information in the "Troubleshooting" section of the
document.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Apr 19 '07 #13

P: n/a
<ch**********@hotmail.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
On Apr 18, 5:38 pm, "Willy Denoyette [MVP]"
<willy.denoye...@telenet.bewrote:
><chuckie_9...@hotmail.comwrote in message

news:11**********************@b58g2000hsg.googleg roups.com...


On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
h...@gmx.atwrote:
<chuckie_9...@hotmail.comschrieb:
I am struggling with releasing com objects. I
have isolated the problem to the code below. Objects are released and
the process ends until I
use "int k = sheet.Count;" Then the process does not end.
>PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-"Troubleshooting"
>--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
thank you for your resposnse and I have to admit I am somewhat
confused (I guess that's obvious). I read the article and maybe I
missed the obvious, but I do use GC.Collect() and
GC.WaitForPendingFinalizers() and excelapplication.quit(). And I
think I understand that I must explicitly reference any object that is
implicitly created. So, I think (I am probably wrong) int k =
sheet.Count; creates an implicit reference that I must explicitly
reference so I can remove the reference. Is this correct? If so , how
do I do that? If I am wrong, please correct me. Everything works
fine until this statement is added. Thank you

No, Count does not create an reference to a COM object, sheet holds a reference to the
COM
interface.
But as long as you don't post a *complete* sample, that illustrates the issue, you won't
get
any sensible answers.

A complete sample looks something like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Reflection;

using Exl = Microsoft.Office.Interop.Excel;
namespace OffExc
{
class Program
{
[STAThread]
static void Main(string[] args)
{
System.Threading.Thread.CurrentThread.CurrentCultu re = new CultureInfo(
"en-US",
false );
Exl.Application exApp = new
Microsoft.Office.Interop.Excel.ApplicationClass() ;
// Reference 1
Exl.Workbook wb = exApp.Workbooks.Add(Missing.Value); // Reference 2
exApp.Visible = true;
// Keep Excel visible for a while..
System.Threading.Thread.Sleep(3000);
Exl.Sheets sheet = wb.Worksheets; // // Reference 3
int k = sheet.Count;
// Quit
exApp.Quit();
// Release the three COM references...
System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(exApp);
GC.Collect();
// Let the finalizer run, this one will delete the unmanaged COM wrappers,
// if for one or another reason, the finalizer cannot run to completion,
// chances are that the Excel process won't get removed !!!!!!!!
GC.WaitForPendingFinalizers();
// Excel should be gone by now... keep the console processrunning for a while
System.Threading.Thread.Sleep(30000);
}
}

}

And above code works as expected on my box.

Willy.- Hide quoted text -

- Show quoted text -

thank you gentlmen for your help. the following seems to be working
correctly. of course I have alot more to do but this will get me
started. Is there a "better" way to save the workbook (ie, use
wb.saveas) and is there a better way way to enumerate and delete
unwanted sheets? Also, I realize error checking needs to be
implemented. Thanks again for your assistance.

using System;
using System.Reflection;
using System.Threading;
using System.Globalization;
using Exl = Microsoft.Office.Interop.Excel;

namespace mynamespace
{
class myexcel
{
public void test()
{
System.Threading.Thread.CurrentThread.CurrentCultu re = new
CultureInfo("en-US", false);
Exl.Application exApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
// Reference 1
Exl.Workbook wb = exApp.Workbooks.Add(Missing.Value); //
Reference 2
exApp.Visible = true;
Exl.Sheets sheet = wb.Worksheets; // Reference 3
// set active sheet
Exl.Worksheet activesheet = (Exl.Worksheet)wb.ActiveSheet;
activesheet.Name = "testsheet";
// write to cell A1
Exl.Range range;
range = activesheet.get_Range("A1", Missing.Value);
range.Value2 = "test input";

System.Runtime.InteropServices.Marshal.ReleaseComO bject(range);
// disable alerts so I'm not prompted when a worksheet is
deleted
exApp.DisplayAlerts = false;
// delete a sheet
for (int i = 1; i <= sheet.Count; i++)
{
// would like to delete
Exl.Worksheet ob = (Exl.Worksheet)exApp.Worksheets[i];
if (ob.Name.ToLower().Substring(0, 5).Equals("sheet"))
ob.Delete();
ReleaseComObject(ob);
ob = null;
}
// save the workbook
exApp.ActiveWorkbook.Close(true, @"c:\testxls.xls",
Missing.Value);
// Quit
exApp.Quit();
// Release the COM references...
ReleaseComObject(activesheet);
ReleaseComObject(sheet);
ReleaseComObject(wb);
ReleaseComObject(exApp);
GC.Collect();
// Let the finalizer run, this one will delete the unmanaged
COM wrappers,
// if for one or another reason, the finalizer cannot run to
completion,
// chances are that the Excel process won't get
removed !!!!!!!!
GC.WaitForPendingFinalizers();
// Excel should be gone by now... keep the console
processrunning for a while
System.Threading.Thread.Sleep(3000);
}
public void ReleaseComObject(Object reference)
{
try
{
while
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(reference) >
-1) ;
}
catch (Exception ex)
{
// handle exception
}
finally
{
reference = null;
}
}
}
}


No need to make it that complicated, each sheet will share the same COM object reference,
that means that you only have to release it once when done.

// delete default sheets
Exl.Worksheet ob = null;
for (int i = 1; i <= sheet.Count; i++)
{
// would like to delete
ob = (Exl.Worksheet)exApp.Worksheets[i];
if(ob.Name.StartsWith("Sheet")) ob.Delete();
}
// release the single Worksheet COM interface used by ob ...
System.Runtime.InteropServices.Marshal.ReleaseComO bject(ob);
// create new sheet...
No need for the ReleaseComObject method either.

Willy.

Apr 19 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.