473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Excel Process not terminating

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
18 3050
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mark | last post by:
Hi all, as many had, i have some problems terminating the excel-process. I implemented the solutions i found in this forums. Tried most combinations of them ;-). It still doesn't work. I'll...
0
by: lgbjr | last post by:
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...
27
by: jeniffer | last post by:
I need to create an excel file through a C program and then to populate it.How can it be done?
13
by: chuckie_9497 | last post by:
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...
0
by: ravindarjobs | last post by:
dear friends, i am using ms access 2003. i wanted to export data from access table to excel table. so i have followed this Dim db As Database Dim ea As Excel.Application Dim rs As...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.