1

I've read every post I could find on this, and THOUGHT I was handling it correctly. But I've got one Excel COM process that still keeps hanging around. I know where it is (see COMMENT IN caps), and I know why it's hanging around (it uses a Split(Range) call, creating two COM objects).

But I still can't figure out how to kill the second object. I thought waiting for pending finalizers by the GC would take care of both of them, but no luck. What am I missing? 

Private Sub PopulateSensorSizes(ByVal newContFlag As Boolean)

    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim Values() As String

    oExcel = New Excel.Application                          'Create a new instance of Excel
    oBook = oExcel.Workbooks.Open(ControllerParamFilePath)  'Get correct existing Controller file
    oSheet = oExcel.Worksheets(1)
    oExcel.Visible = False                                  'Don't show it to the user

    'Show sensor sizes
    Values = Split(oSheet.Range("B2").Value, "," & " ")     'Split text. TWO COM OBJECTS LAUNCHED HERE
    Dim i As Integer
    For i = 0 To UBound(Values)
        SelectSensorSize.Items.Add(Values(i))               'Add all integer sensor sizes to ListBox1
    Next i

    oBook.Close()                                           'Disconnect from Excel and end processes
    oExcel.Quit()
    oBook = Nothing
    oExcel = Nothing
    GC.Collect()
    GC.WaitForPendingFinalizers()

    SelectSensorSize.Items.Add("Enter new sensor size")

End Sub

EDIT: I tried rewriting this to attempt to eliminate the "two dot" issue, but I am getting the same result: one process left running. 

Private Sub PopulateSensorSizes(ByVal newContFlag As Boolean)

    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim Values() As String
    Dim ValRange as Excel.Range

    oExcel = New Excel.Application                          'Create a new instance of Excel
    oBook = oExcel.Workbooks.Open(ControllerParamFilePath)  'Get correct existing Controller file
    oSheet = oExcel.Worksheets(1)
    oExcel.Visible = False                                  'Don't show it to the user

    'Show sensor sizes
    ValRange = oSheet.Range("B2")
    Values = Split(ValRange.Value, "," & " ")     'Split text. TWO COM OBJECTS LAUNCHED HERE
    Dim i As Integer
    For i = 0 To UBound(Values)
        SelectSensorSize.Items.Add(Values(i))               'Add all integer sensor sizes to ListBox1
    Next i

    oBook.Close()                                           'Disconnect from Excel and end processes
    oExcel.Quit()
    oBook = Nothing
    oExcel = Nothing
    GC.Collect()
    GC.WaitForPendingFinalizers()

    SelectSensorSize.Items.Add("Enter new sensor size")

End Sub
nobby
  • 373
  • 1
  • 3
  • 15
  • What's in the split function? Have you tried separating the function to see where exactly it create two object? If it's with the call to Range, you could try using a different function like .Cells(2, 2).Value – the_lotus Jan 21 '16 at 16:48
  • The Split function is a standard MS function: [link]https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx?f=255&MSPPError=-2147217396 – nobby Jan 21 '16 at 16:51
  • I also attempting relocating the GC calls to AFTER where this method was called from, as suggested in another thread, and still the same result: one process left running. – nobby Jan 21 '16 at 17:57
  • The thread I was referring to above was this one by Hans Passant: http://stackoverflow.com/questions/14222501/excel-process-not-closing-in-vb-net – nobby Jan 21 '16 at 18:05
  • Interesting that you *solved* your own issue. How do you call the split function without using a reference to an existing excel workbook/application/etc. Maybe if you could figure out how to call it without it having to create another instance or if you could get ahold of that instance, you would be able to clean it up. – Steve Jan 21 '16 at 21:21
  • Or, maybe you could use the 'String.Split()' function and not use the one from excel at all which would eliminate the second instance running. – Steve Jan 21 '16 at 21:23

0 Answers0