excel process not terminating properly

[chuckie_9497]

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 )excelapplicati on.ActiveWorkbo ok;
Excel.Sheets sheet = workbook.Worksh eets;

// problem here
int k = sheet.Count;

System.Runtime. InteropServices .Marshal.Releas eComObject(shee t);
sheet = null;
System.Runtime. InteropServices .Marshal.Releas eComObject(work book);
workbook = null;

[chuckie_9497]

On Apr 18, 5:38 pm, "Willy Denoyette [MVP]"
<willy.denoye.. [e-mail] te:

<chuckie_9...@h otmail.comwrote in message

news:11******** **************@ b58g2000hsg.goo glegroups.com.. .

On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
[e-mail] e:

<chuckie_9...@h otmail.comschri eb:

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.microso ft.com/?scid=kb;EN-US;317109>
-"Troubleshootin g"

--
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.WaitForPendi ngFinalizers() and excelapplicatio n.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.Collecti ons.Generic;
using System.Text;
using System.Globaliz ation;
using System.Reflecti on;

using Exl = Microsoft.Offic e.Interop.Excel ;
namespace OffExc
{
class Program
{
[STAThread]
static void Main(string[] args)
{
System.Threadin g.Thread.Curren tThread.Current Culture = new CultureInfo( "en-US",
false );
Exl.Application exApp = new Microsoft.Offic e.Interop.Excel .ApplicationCla ss();
// Reference 1
Exl.Workbook wb = exApp.Workbooks .Add(Missing.Va lue); // Reference 2
exApp.Visible = true;
// Keep Excel visible for a while..
System.Threadin g.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.Releas eComObject(shee t);
System.Runtime. InteropServices .Marshal.Releas eComObject(wb);
System.Runtime. InteropServices .Marshal.Releas eComObject(exAp p);
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.WaitForPendi ngFinalizers();
// Excel should be gone by now... keep the console processrunning for a while
System.Threadin g.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.Reflecti on;
using System.Threadin g;
using System.Globaliz ation;
using Exl = Microsoft.Offic e.Interop.Excel ;

namespace mynamespace
{
class myexcel
{
public void test()
{
System.Threadin g.Thread.Curren tThread.Current Culture = new
CultureInfo("en-US", false);
Exl.Application exApp = new
Microsoft.Offic e.Interop.Excel .ApplicationCla ss();
// Reference 1
Exl.Workbook wb = exApp.Workbooks .Add(Missing.Va lue); //
Reference 2
exApp.Visible = true;
Exl.Sheets sheet = wb.Worksheets; // Reference 3
// set active sheet
Exl.Worksheet activesheet = (Exl.Worksheet) wb.ActiveSheet;
activesheet.Nam e = "testsheet" ;
// write to cell A1
Exl.Range range;
range = activesheet.get _Range("A1", Missing.Value);
range.Value2 = "test input";

System.Runtime. InteropServices .Marshal.Releas eComObject(rang e);
// disable alerts so I'm not prompted when a worksheet is
deleted
exApp.DisplayAl erts = false;
// delete a sheet
for (int i = 1; i <= sheet.Count; i++)
{
// would like to delete
Exl.Worksheet ob = (Exl.Worksheet) exApp.Worksheet s[i];
if (ob.Name.ToLowe r().Substring(0 , 5).Equals("shee t"))
ob.Delete();
ReleaseComObjec t(ob);
ob = null;
}
// save the workbook
exApp.ActiveWor kbook.Close(tru e, @"c:\testxls.xl s",
Missing.Value);
// Quit
exApp.Quit();
// Release the COM references...
ReleaseComObjec t(activesheet);
ReleaseComObjec t(sheet);
ReleaseComObjec t(wb);
ReleaseComObjec t(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.WaitForPendi ngFinalizers();
// Excel should be gone by now... keep the console
processrunning for a while
System.Threadin g.Thread.Sleep( 3000);
}
public void ReleaseComObjec t(Object reference)
{
try
{
while
(System.Runtime .InteropService s.Marshal.Relea seComObject(ref erence) >
-1) ;
}
catch (Exception ex)
{
// handle exception
}
finally
{
reference = null;
}
}
}
}

[Scott M.]

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******** ******@TK2MSFTN GP06.phx.gbl...

"Scott M." <s-***@nospam.nosp amschrieb:

>Your link shows (in a more elaborate way) how to do what the OP is
already doing (RleaseCOMObjec t 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/>

[Herfried K. Wagner [MVP]]

"Scott M." <s-***@nospam.nosp amschrieb:

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 "Troubleshootin g" 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/>

[Willy Denoyette [MVP]]

<ch**********@h otmail.comwrote in message
news:11******** **************@ q75g2000hsh.goo glegroups.com.. .

On Apr 18, 5:38 pm, "Willy Denoyette [MVP]"
<willy.denoye.. [e-mail] te:

><chuckie_9...@ hotmail.comwrot e in message

news:11******* *************** @b58g2000hsg.go oglegroups.com. ..

On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
[e-mail] e:
<chuckie_9...@ hotmail.comschr ieb:

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.microso ft.com/?scid=kb;EN-US;317109>
-"Troubleshootin g"

>--
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.WaitForPendi ngFinalizers() and excelapplicatio n.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.Collecti ons.Generic;
using System.Text;
using System.Globaliz ation;
using System.Reflecti on;

using Exl = Microsoft.Offic e.Interop.Excel ;
namespace OffExc
{
class Program
{
[STAThread]
static void Main(string[] args)
{
System.Threadin g.Thread.Curren tThread.Current Culture = new CultureInfo(
"en-US",
false );
Exl.Application exApp = new
Microsoft.Offi ce.Interop.Exce l.ApplicationCl ass();
// Reference 1
Exl.Workbook wb = exApp.Workbooks .Add(Missing.Va lue); // Reference 2
exApp.Visible = true;
// Keep Excel visible for a while..
System.Threadin g.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.Releas eComObject(shee t);
System.Runtime. InteropServices .Marshal.Releas eComObject(wb);
System.Runtime. InteropServices .Marshal.Releas eComObject(exAp p);
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.WaitForPendi ngFinalizers();
// Excel should be gone by now... keep the console processrunning for a while
System.Threadin g.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.Reflecti on;
using System.Threadin g;
using System.Globaliz ation;
using Exl = Microsoft.Offic e.Interop.Excel ;

namespace mynamespace
{
class myexcel
{
public void test()
{
System.Threadin g.Thread.Curren tThread.Current Culture = new
CultureInfo("en-US", false);
Exl.Application exApp = new
Microsoft.Offic e.Interop.Excel .ApplicationCla ss();
// Reference 1
Exl.Workbook wb = exApp.Workbooks .Add(Missing.Va lue); //
Reference 2
exApp.Visible = true;
Exl.Sheets sheet = wb.Worksheets; // Reference 3
// set active sheet
Exl.Worksheet activesheet = (Exl.Worksheet) wb.ActiveSheet;
activesheet.Nam e = "testsheet" ;
// write to cell A1
Exl.Range range;
range = activesheet.get _Range("A1", Missing.Value);
range.Value2 = "test input";

System.Runtime. InteropServices .Marshal.Releas eComObject(rang e);
// disable alerts so I'm not prompted when a worksheet is
deleted
exApp.DisplayAl erts = false;
// delete a sheet
for (int i = 1; i <= sheet.Count; i++)
{
// would like to delete
Exl.Worksheet ob = (Exl.Worksheet) exApp.Worksheet s[i];
if (ob.Name.ToLowe r().Substring(0 , 5).Equals("shee t"))
ob.Delete();
ReleaseComObjec t(ob);
ob = null;
}
// save the workbook
exApp.ActiveWor kbook.Close(tru e, @"c:\testxls.xl s",
Missing.Value);
// Quit
exApp.Quit();
// Release the COM references...
ReleaseComObjec t(activesheet);
ReleaseComObjec t(sheet);
ReleaseComObjec t(wb);
ReleaseComObjec t(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.WaitForPendi ngFinalizers();
// Excel should be gone by now... keep the console
processrunning for a while
System.Threadin g.Thread.Sleep( 3000);
}
public void ReleaseComObjec t(Object reference)
{
try
{
while
(System.Runtime .InteropService s.Marshal.Relea seComObject(ref erence) >
-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.Worksheet s[i];
if(ob.Name.Star tsWith("Sheet") ) ob.Delete();
}
// release the single Worksheet COM interface used by ob ...
System.Runtime. InteropServices .Marshal.Releas eComObject(ob);
// create new sheet...
No need for the ReleaseComObjec t method either.

Willy.