excel process not terminating properly

C

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)excelapplication.ActiveWorkbook;
Excel.Sheets sheet = workbook.Worksheets;

// problem here
int k = sheet.Count;

System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
 
S

Scott M.

Try adding the following just BEFORE your call ReleaseCOMObject on them:

sheet = null;
workbook = null;
 
H

Herfried K. Wagner [MVP]

Scott M. said:
Try adding the following just BEFORE your call ReleaseCOMObject on them:

sheet = null;
workbook = null;
Click to expand...

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

Scott M.

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?
 
H

Herfried K. Wagner [MVP]

Scott M. said:
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?
Click to expand...

I suggest the "more elaborate way".
 
C

chuckie_9497

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

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
 
C

chuckie_9497

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

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
 
W

Willy Denoyette [MVP]

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
Click to expand...


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.CurrentCulture = 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.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.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(30000);
}
}
}

And above code works as expected on my box.

Willy.
 
P

PFC Sadr

this is a scam

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

ADO works like a charm
 
C

chuckie_9497

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
Click to expand...

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.CurrentCulture = 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.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.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(30000);
}
}

}

And above code works as expected on my box.

Willy.- Hide quoted text -

- Show quoted text -
Click to expand...

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.CurrentCulture = 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.ReleaseComObject(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;
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.ReleaseComObject(reference) >
-1) ;
}
catch (Exception ex)
{
// handle exception
}
finally
{
reference = null;
}
}
}
}
 
S

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?
 
H

Herfried K. Wagner [MVP]

Scott M. said:
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?
Click to expand...

Take a look at the information in the "Troubleshooting" section of the
document.
 
W

Willy Denoyette [MVP]

On Apr 18, 3:48 pm, "Herfried K. Wagner [MVP]" <hirf-spam-me-
(e-mail address removed)> wrote:
<[email protected]> schrieb:
Click to expand...
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.
Click to expand...
PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-> "Troubleshooting"
Click to expand...
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
Click to expand...

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.CurrentCulture = 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.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.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(30000);
}
}

}

And above code works as expected on my box.

Willy.- Hide quoted text -

- Show quoted text -
Click to expand...

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.CurrentCulture = 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.ReleaseComObject(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;
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.ReleaseComObject(reference) >
-1) ;
}
catch (Exception ex)
{
// handle exception
}
finally
{
reference = null;
}
}
}
}
Click to expand...





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;
if(ob.Name.StartsWith("Sheet")) ob.Delete();
}
// release the single Worksheet COM interface used by ob ...
System.Runtime.InteropServices.Marshal.ReleaseComObject(ob);
// create new sheet...


No need for the ReleaseComObject method either.

Willy.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ASP.Net and Excel Process is not killed. Unmanged code problems 2
How can I kill an "Excel" process from a memory? 1
Orphaned Excel 97 Instances (C#) 0
reading from text file to excel file 1
Using Excel With Python 0
Closing Excel on server 0
Too many number of excel processes are shown in the process 1
excel is not closed in runtime using excel.Quit() 7

Members online

Total: 73 (members: 4, guests: 69)
Robots: 491

Forum statistics

Threads
473,995
Messages
2,570,225
Members
46,815
Latest member
treekmostly22

Latest Threads

Top