Excel 2002 won't die, using Automation

[l33t_c0w]

Slightly complex scenario maybe, so please bear with me as I try to explain it. The actual question will be at the end, but needs an introduction.

This is a small program I'm writing to query an SQL server, get a bunch of information, and then write to an Excel sheet and format the data a bit (stuff like certain rows being different colors, some cells in bold, a few page breaks).

The spreadsheet I'm writing to is in a pre-made blank workbook sitting in the same directory as the program itself, that has macros in it. Initially I just wanted to write all the information, do an objExcel.visible = true, and then let the user play with it. The thing I noticed though was that there was a major slowdown with the macros contained in the spreadsheet. Things take a few seconds each now where before they took a small fraction of a second. It really isn't acceptable.

So, then I decided to instead try to just save the spreadsheet and close excel to get it "unhooked" from the visual basic program. The problem is, the process won't die. I visited http://support.microsoft.com/default...b;en-us;317109 and tried applying that to my program. Now, when I leave the program running and create additional spreadsheets, I can watch processes spawn and then die, but the very first process always stays. It causes Excel to hang / crash when I try to open a file I'd created with the program during that session. If I kill it there seem to be no ill effects and then everything runs well, but that isn't an option unless I can be sure that I'm killing the right process. I figure there's either a glitch or I'm doing something dumb. Since I'm pretty new to programming in general, I wouldn't rule out either.

Has anyone dealt with persisting evil excel processes and automation before?

edit: forgot the code.

Private Sub cmdBuildSheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBuildSheet.Click
 
        Dim ExcelArr As Object(,)
        Dim colsCount As Integer
        Dim rowsCount As Integer
        Dim objExcel As Excel.Application
        Dim objWorkbooks As Excel.Workbooks
        Dim objWorkbook As Excel.Workbook
        Dim objSheet As Excel.Worksheet
        Dim objRange As Excel.Range
 
        Try
 
            radNoWorkOrders.Enabled = False
            radWorkOrders.Enabled = False
 
            objExcel = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            objWorkbooks = objExcel.Workbooks
            objWorkbook = objExcel.Workbooks.Open(Application.StartupPath & "\NOTOUCH.xls")
            objSheet = DirectCast(objWorkbook.ActiveSheet, Excel.Worksheet)
 
            _arrIndex = 1 
 
            If radWorkOrders.Checked = True Then 
 
                ReDim _PartsArr(11, 39)
 
                _PartsArr(0, 1) = "Parent"
                _PartsArr(1, 1) = cboPart.Text
                _PartsArr(3, 1) = txtDescription.Text
 
                GetPartsWO(cboPart.Text, 1)
 
                objRange = objSheet.Range("A:D")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("E:G")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("H:H")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("I:I")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("J:K")
                objRange.NumberFormat = "@" 
 
                Dim iterateArr As Integer = _PartsArr.GetUpperBound(1)
                Do Until Not _PartsArr(1, iterateArr) = "" Or Not _PartsArr(9, iteratearr) = ""
                    iterateArr = iterateArr - 1
                Loop
                rowsCount = iterateArr
 
            ElseIf radNoWorkOrders.Checked = True Then  
                ReDim _PartsArr(10, 39)
 
                _PartsArr(0, 1) = "Parent"
                _PartsArr(1, 1) = cboPart.Text 
                _PartsArr(3, 1) = txtDescription.Text
 
                GetParts(cboPart.Text, 1)
 
                objRange = objSheet.Range("A:D")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("E:E")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("F:F")
                objRange.NumberFormat = "0.00" 
                objRange = objSheet.Range("G:H")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("I:I")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("J:J")
                objRange.NumberFormat = "0" 
 
                Dim iterateArr As Integer = _PartsArr.GetUpperBound(1)
                Do Until Not _PartsArr(1, iterateArr) = ""
                    iterateArr = iterateArr - 1
                Loop
                rowsCount = iterateArr
 
            End If 
 
            colsCount = _PartsArr.GetUpperBound(0)
 
            ReDim ExcelArr(rowsCount, colsCount)
            For iterate1 As Integer = 0 To rowsCount
                For iterate2 As Integer = 0 To colsCount - 1 
                    ExcelArr(iterate1, iterate2) = _PartsArr(iterate2, iterate1)
                Next
            Next
 
            Dim objPageSetup As Excel.PageSetup = objSheet.PageSetup
            objPageSetup.TopMargin = 55 
            objPageSetup.HeaderMargin = 18
            objPageSetup.BottomMargin = 0
            objPageSetup.FooterMargin = 0
            objPageSetup.LeftMargin = 0
            objPageSetup.RightMargin = 0
            objPageSetup.PrintTitleRows = "A1:K1"
 
            objRange = objSheet.Range("A1:A1")
            objRange.Resize(rowsCount + 1, colsCount).Font.Size = 8
            objRange = objSheet.Range("A2:A2")
            objRange.Resize(rowsCount, colsCount).Borders.Value = 1
 
            objRange = objSheet.Range("A1:A1")
            objRange.Resize(rowsCount + 1, colsCount + 1).Value = ExcelArr
 
            For iterateColors As Integer = 1 To rowsCount + 1
                Select Case _PartsArr(colsCount, iterateColors - 1)
                    Case Is = "PU  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(255, 153, 204)
                    Case Is = "MA  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(255, 255, 0)
                    Case Is = "FA  "
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(153, 204, 0)
                    Case Is = "CO  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(153, 204, 255)
                End Select
            Next
 
            Dim objPageBreaks As Excel.HPageBreaks = objSheet.HPageBreaks
            Dim pageFlag As Boolean = False 
            For iterateRows As Integer = 1 To rowsCount + 1
                If _PartsArr(0, iterateRows - 1) = "  1" Then
                    objRange = objSheet.Range("A" & iterateRows.ToString)
                    objRange.Resize(1, colsCount).Font.Bold = True
                    If pageFlag = True Then
                        objPageBreaks.Add(objSheet.Cells(iterateRows, 1))
                    Else
                        pageFlag = True
                    End If
                End If
            Next
 
            objRange = objSheet.Range("A1:A1")
            objRange.RowHeight = 25.5
 
            If radWorkOrders.Checked = True Then
                objRange = objSheet.Range("F1:G1")
                objRange.WrapText = True
                objRange = objSheet.Range("F1:G1")
                objRange.ColumnWidth = 5 
                objSheet.Cells.EntireColumn.AutoFit()
                objRange = objSheet.Range("H:H")
                objRange.ColumnWidth = 8
            Else
                objRange = objSheet.Range("G1:H1")
                objRange.WrapText = True
                objRange = objSheet.Range("G1:H1")
                objRange.ColumnWidth = 5
                objSheet.Cells.EntireColumn.AutoFit()
            End If
 
            objWorkbook.SaveAs(Application.StartupPath & "\" & _PartsArr(1, 1) & ".xls")
 
            NAR(objPageBreaks)
            NAR(objPageSetup)
            NAR(objRange)
            NAR(objSheet)
            objWorkbook.Close(False)
            NAR(objWorkbook)
            NAR(objWorkbooks)
            objExcel.Quit()
            NAR(objExcel)
 
            GC.Collect()
            GC.WaitForPendingFinalizers()
 
            MessageBox.Show("Completion!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace)
        Finally
            radWorkOrders.Enabled = True
            radNoWorkOrders.Enabled = True
        End Try
    End Sub
 
    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            o = Nothing
        End Try
    End Sub