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

Excel Process not terminating

P: n/a
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel. I
found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The excel
workbook, worksheets, and all of the cells are properly formatted when Excel
quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub
Nov 21 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #2

P: n/a
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #3

P: n/a
Hi All,

OK, I've been doing some experimenting. If I add an additional

NAR(oSheet)

then I can name the sheets using

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

and the Excel Process terminates. However, sections 2 and 3 of the code
still leave the Excel process running. Since I obviously don't understand
what COMobjects need to be released to get the Excel process to exit, maybe
there's a better way to approach this problem.

Does anyone know how to retrieve a list of active COMobjects? This way, I
could see what I need to release.

thanks
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF",
"Working", "With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub


Nov 21 '05 #4

P: n/a
Hi All,

OK, I've been doing some experimenting. If I add an additional

NAR(oSheet)

then I can name the sheets using

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

and the Excel Process terminates. However, sections 2 and 3 of the code
still leave the Excel process running. Since I obviously don't understand
what COMobjects need to be released to get the Excel process to exit, maybe
there's a better way to approach this problem.

Does anyone know how to retrieve a list of active COMobjects? This way, I
could see what I need to release.

thanks
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF",
"Working", "With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub


Nov 21 '05 #5

P: n/a
Lgbjr

You mean this one?

Excel object model
http://msdn.microsoft.com/library/de...l/ExcelObj.asp

I hope this helps?

Cor
Nov 21 '05 #6

P: n/a
Lgbjr

You mean this one?

Excel object model
http://msdn.microsoft.com/library/de...l/ExcelObj.asp

I hope this helps?

Cor
Nov 21 '05 #7

P: n/a
Cor,

Not exactly, but thanks for the link. What I need to get more information on
is the COM objects that are created by the code that I posted in this
thread. Via the NAR sub, I am attempting to release all of the COM objects
created in the process of doing all of the excel stuff, but I am apparently
missing one (or more), because the Excel process is not being terminated
when the Excel window closes.

It might be that I'm missing a COM object, or it might be that I'm releasing
them in the wrong order. I don't know.

Cheers,
Lee

"Cor Ligthert" <no************@planet.nl> wrote in message
news:u6**************@TK2MSFTNGP10.phx.gbl...
Lgbjr

You mean this one?

Excel object model
http://msdn.microsoft.com/library/de...l/ExcelObj.asp

I hope this helps?

Cor

Nov 21 '05 #8

P: n/a
Cor,

Not exactly, but thanks for the link. What I need to get more information on
is the COM objects that are created by the code that I posted in this
thread. Via the NAR sub, I am attempting to release all of the COM objects
created in the process of doing all of the excel stuff, but I am apparently
missing one (or more), because the Excel process is not being terminated
when the Excel window closes.

It might be that I'm missing a COM object, or it might be that I'm releasing
them in the wrong order. I don't know.

Cheers,
Lee

"Cor Ligthert" <no************@planet.nl> wrote in message
news:u6**************@TK2MSFTNGP10.phx.gbl...
Lgbjr

You mean this one?

Excel object model
http://msdn.microsoft.com/library/de...l/ExcelObj.asp

I hope this helps?

Cor

Nov 21 '05 #9

P: n/a
Hi LG,

Here's what you're missing:
Marshal.ReleaseComObject(xlapp)

HTH,

Bernie Yaeger

"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #10

P: n/a
Hi LG,

Here's what you're missing:
Marshal.ReleaseComObject(xlapp)

HTH,

Bernie Yaeger

"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #11

P: n/a
Hi LG,

I take it back - I see you have that call; don't know what you're missing
then!

Bernie

"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #12

P: n/a
Hi LG,

I take it back - I see you have that call; don't know what you're missing
then!

Bernie

"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF", "Working",
"With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

Nov 21 '05 #13

P: n/a
Hi,
Have you tried releasing the oSheet references as soon as you're finished with them *and then*
assign them to a new sheet?
--
HTH
Dan Artuso, Access MVP
"lgbjr" <lg***@online.nospam> wrote in message news:u0**************@TK2MSFTNGP09.phx.gbl...
Hi All,

OK, I've been doing some experimenting. If I add an additional

NAR(oSheet)

then I can name the sheets using

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

and the Excel Process terminates. However, sections 2 and 3 of the code
still leave the Excel process running. Since I obviously don't understand
what COMobjects need to be released to get the Excel process to exit, maybe
there's a better way to approach this problem.

Does anyone know how to retrieve a list of active COMobjects? This way, I
could see what I need to release.

thanks
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF",
"Working", "With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub



Nov 21 '05 #14

P: n/a
Hi,
Have you tried releasing the oSheet references as soon as you're finished with them *and then*
assign them to a new sheet?
--
HTH
Dan Artuso, Access MVP
"lgbjr" <lg***@online.nospam> wrote in message news:u0**************@TK2MSFTNGP09.phx.gbl...
Hi All,

OK, I've been doing some experimenting. If I add an additional

NAR(oSheet)

then I can name the sheets using

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

and the Excel Process terminates. However, sections 2 and 3 of the code
still leave the Excel process running. Since I obviously don't understand
what COMobjects need to be released to get the Excel process to exit, maybe
there's a better way to approach this problem.

Does anyone know how to retrieve a list of active COMobjects? This way, I
could see what I need to release.

thanks
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
Hi All,

OK, here is some more specific information about where the problem is
occuring.

the code below has 3 basic sections. the first adds 3 worksheets and a set
of 3 headers (row 1) on each sheet. The second section adds some data to
these columns. The third section adds some formatting.

If I get rid of the second and third sections, the Excel process still
doesn't terminate as it should. however, if I also don't name the sheets
that are created in the first section, the Excel process terminates as it
should.

So, the first major hurddle is finding out why

oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)

causes the Excel process not to terminate.

regards,
Lee
"lgbjr" <lg***@online.nospam> wrote in message
news:Oo****************@tk2msftngp13.phx.gbl...
Hi All,

I have a VB.NET app that, among other things, writes data to Excel. I am
having trouble getting the Excel process to terminate after I quit Excel.
I found an article related to this problem:

http://support.microsoft.com/default...b;en-us;317109

Below is a sample of code that I wrote based on the above article. The
excel workbook, worksheets, and all of the cells are properly formatted
when Excel quits, but the process will not terminate.

I've tried the code as shown in the above article and the process does
terminate, but it seems that as soon as you do something to modify the
workbook, the Excel process won't terminate.

any ideas??

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim c, i, j, r, s, lastcol As Integer
Dim Sname() As String = {"Hello", "World", "Bye"}
Dim T_head As New ArrayList
Dim Lee() As Integer = {0, 1, 2}
Dim Jeanie() As Integer = {3, 4, 5}
Dim we() As Integer = {6, 7, 8}
Dim HIndex() = {Lee, Jeanie, we}
Dim head() As String = {"This", "Is", "A", "Test", "OF",
"Working", "With", "Excel", "Code"}
Dim Rarray As New ArrayList
Dim T_Rarray As New ArrayList
Dim oApp As New Excel.Application
oApp.SheetsInNewWorkbook = 1
Dim oBooks As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oBooks.Add
Dim oSheet As Excel.Worksheet
Dim rng As Excel.Range

oApp.Visible = True
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet
oSheet.Name = Sname(s)
For i = 0 To HIndex(s).Length - 1
T_head.Add(head(HIndex(s)(i)))
Next
For c = 0 To T_head.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2 =
T_head(c)
Next
T_head.Clear()
Next
For j = 1 To 3
r = r + 1
For i = 1 To 9
Rarray.Add(i * j)
Next
For s = 0 To 2
For i = 0 To HIndex(s).Length - 1
T_Rarray.Add(Rarray(HIndex(s)(i)))
Next
For c = 0 To T_Rarray.Count - 1
oSheet.Select(s + 1)
CType(oSheet.Cells.Item(r, c + 1), Excel.Range).Value2
= T_Rarray(c)
Next
T_Rarray.Clear()
Next
Rarray.Clear()
Next
For s = 0 To 2
oSheet.Select(s + 1)
lastcol = oSheet.UsedRange.Find("*", , , ,
Excel.XlSearchOrder.xlByColumns,
Excel.XlSearchDirection.xlPrevious).Column
oSheet.UsedRange.HorizontalAlignment = 3
If (s = 0) Then
oSheet.UsedRange.NumberFormat = 0
End If
oSheet.UsedRange.Columns.AutoFit()

For i = 1 To lastcol
rng = oSheet.Cells(1, i)
rng.Font.Bold = True
rng.Interior.ColorIndex = 15
Next
Next
NAR(rng)
NAR(oSheet)
oBook.Close(False)
NAR(oBook)
NAR(oBooks)
Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
oApp.Quit()
NAR(oApp)
GC.Collect()
Debug.WriteLine("End Excel")

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub



Nov 21 '05 #15

P: n/a
Hi

I do not think we can get such a list.
Because the Excel is a COM Object, in the COM category, we need to add a
reference to the COM Library for the lifetime control purpose(Once there is
reference on the COM library the library will not exit). And the .NET call
the Excel via the RCW which also follow the rule.

You may try to take a loo at the Marshal.ReleaseComObject Method in the
MSDN.

Note To ensure that the runtime callable wrapper and the original COM
object are released, construct a loop from which you call this method until
the returned reference count reaches zero.

Because we can access to the Excel Object Modal via the syntax similar with
below.
A.B.C

So when we run the code, the RCW will underlying add a reference to the
B,C(called indirect reference), and that is why we need to declare the
worksheet, workbook ... seperately.

I think for this issue, you may try to avoiding using the A.B.C syntax and
seperate them one by one.
e.g.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet

Here I think we have two worksheet instances.
We would better change it as below.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
Dim oSheet1 As Excel.Worksheet
r = 1
For s = 0 To 2
oSheet1 = oBook.Sheets.Add()

Also if possible, I think you may also try to seperate the
oBook.Sheets.Add() into two.
Dim oSheets As Excel.Worksheets
oSheets = oBook.Sheets
oSheet1 = oSheets.Add()
Hope this helps.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Nov 21 '05 #16

P: n/a
Hi

I do not think we can get such a list.
Because the Excel is a COM Object, in the COM category, we need to add a
reference to the COM Library for the lifetime control purpose(Once there is
reference on the COM library the library will not exit). And the .NET call
the Excel via the RCW which also follow the rule.

You may try to take a loo at the Marshal.ReleaseComObject Method in the
MSDN.

Note To ensure that the runtime callable wrapper and the original COM
object are released, construct a loop from which you call this method until
the returned reference count reaches zero.

Because we can access to the Excel Object Modal via the syntax similar with
below.
A.B.C

So when we run the code, the RCW will underlying add a reference to the
B,C(called indirect reference), and that is why we need to declare the
worksheet, workbook ... seperately.

I think for this issue, you may try to avoiding using the A.B.C syntax and
seperate them one by one.
e.g.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet

Here I think we have two worksheet instances.
We would better change it as below.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
Dim oSheet1 As Excel.Worksheet
r = 1
For s = 0 To 2
oSheet1 = oBook.Sheets.Add()

Also if possible, I think you may also try to seperate the
oBook.Sheets.Add() into two.
Dim oSheets As Excel.Worksheets
oSheets = oBook.Sheets
oSheet1 = oSheets.Add()
Hope this helps.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Nov 21 '05 #17

P: n/a
In case this hasn't been mentioned, don't forget to use the
Excel.Application.Quit method. Excel won't know to quit otherwise.

Mike Ober.

""Peter Huang" [MSFT]" <v-******@online.microsoft.com> wrote in message
news:mz*************@cpmsftngxa10.phx.gbl...
Hi

I do not think we can get such a list.
Because the Excel is a COM Object, in the COM category, we need to add a
reference to the COM Library for the lifetime control purpose(Once there is reference on the COM library the library will not exit). And the .NET call
the Excel via the RCW which also follow the rule.

You may try to take a loo at the Marshal.ReleaseComObject Method in the
MSDN.

Note To ensure that the runtime callable wrapper and the original COM
object are released, construct a loop from which you call this method until the returned reference count reaches zero.

Because we can access to the Excel Object Modal via the syntax similar with below.
A.B.C

So when we run the code, the RCW will underlying add a reference to the
B,C(called indirect reference), and that is why we need to declare the
worksheet, workbook ... seperately.

I think for this issue, you may try to avoiding using the A.B.C syntax and
seperate them one by one.
e.g.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet

Here I think we have two worksheet instances.
We would better change it as below.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
Dim oSheet1 As Excel.Worksheet
r = 1
For s = 0 To 2
oSheet1 = oBook.Sheets.Add()

Also if possible, I think you may also try to seperate the
oBook.Sheets.Add() into two.
Dim oSheets As Excel.Worksheets
oSheets = oBook.Sheets
oSheet1 = oSheets.Add()
Hope this helps.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.


Nov 21 '05 #18

P: n/a
In case this hasn't been mentioned, don't forget to use the
Excel.Application.Quit method. Excel won't know to quit otherwise.

Mike Ober.

""Peter Huang" [MSFT]" <v-******@online.microsoft.com> wrote in message
news:mz*************@cpmsftngxa10.phx.gbl...
Hi

I do not think we can get such a list.
Because the Excel is a COM Object, in the COM category, we need to add a
reference to the COM Library for the lifetime control purpose(Once there is reference on the COM library the library will not exit). And the .NET call
the Excel via the RCW which also follow the rule.

You may try to take a loo at the Marshal.ReleaseComObject Method in the
MSDN.

Note To ensure that the runtime callable wrapper and the original COM
object are released, construct a loop from which you call this method until the returned reference count reaches zero.

Because we can access to the Excel Object Modal via the syntax similar with below.
A.B.C

So when we run the code, the RCW will underlying add a reference to the
B,C(called indirect reference), and that is why we need to declare the
worksheet, workbook ... seperately.

I think for this issue, you may try to avoiding using the A.B.C syntax and
seperate them one by one.
e.g.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
r = 1
For s = 0 To 2
oBook.Sheets.Add()
oSheet = oBook.ActiveSheet

Here I think we have two worksheet instances.
We would better change it as below.
oSheet = oBook.ActiveSheet
oSheet.Name = "Lee"
Dim oSheet1 As Excel.Worksheet
r = 1
For s = 0 To 2
oSheet1 = oBook.Sheets.Add()

Also if possible, I think you may also try to seperate the
oBook.Sheets.Add() into two.
Dim oSheets As Excel.Worksheets
oSheets = oBook.Sheets
oSheet1 = oSheets.Add()
Hope this helps.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.


Nov 21 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.