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 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
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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?
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |