Excel Process not Terminating

[lgbjr]

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.aspx?scid=kb;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.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub

 

[lgbjr]

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]

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

 

[Peter Huang [MSFT]]

Hi

I reviewed the thread and find that there is a similar issue in the
newsgroup below.Now I have replied to you, you may go and take a look.
Subject: Excel Process not terminating
Newsgroups: microsoft.public.dotnet.languages.vb

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.