VB 2010 Express: Yet Another Stubborn Excel Process Won't End

[Vladamir]

I have been over this before in the forum and first off apologize for being so dense on this subject. I really thought I understood it after the first go round.

The project I'm working on was recently revised extensively by the sales team. So in a nutshell, and without being able to post too much of the code due to Non-Disclosure Agreements I'm bound to here is my problem.

I open Excel 2003 with the standard method:

  Dim vExcelApp As Excel.Application = Nothing
  Dim vExcelWorkbooks As Excel.Workbooks = Nothing
  Dim vExcelWorkbook As Excel.Workbook = Nothing
  Dim vExcelSheet1 As Excel.Worksheet = Nothing
  Dim vExcelSheet2 As Excel.Worksheet = Nothing
  Dim vExcelRange1 As Excel.Range = Nothing
  Dim vNovellPath As String = "\\a_very_long_novell_path\"
  Dim vExcelFile As String = NovellPath & "MyExcelFile.xls"

  vExcelApp = New Excel.Application
  vExcelApp.DisplayAlerts = False
  vExcelApp.Visible = False

  vExcelWorkbooks = vExcelApp.Workbooks
  vExcelWorkbook = vExcelApp.Workbooks.Open(vExcelFile)
  vExcelSheet1 = CType(vExcelWorkbook.Sheets("Sheet1"), Excel.Worksheet)
  vExcelSheet2 = CType(vExcelWorkbook.Sheets("Sheet2"), Excel.Worksheet)

At this point, I populate some cells and read calculated data from others. In the current setup this all works well. What's different this time is that in the following code, instead of doing it one time, I loop through it several times until the calculated fields are equal to the desired results. I am using the same variable names each time through the loop so as to not create so many RCW's.... and please don't let that term fool anyone into thinking I know what I'm doing. I have a good understanding of the process but at my level of expertise I get lost very quickly.

   Do Until vExcelSheet1.Range("K6").Value >= vWord(1)
        vExcelRange1 = vExcelSheet1.Range(DimACell)
        vExcelRange1.Value = DimA
        DimA = DimA + vIncrement
   Loop

Now the only difference between this block and the other block which doesn't leave Excel running is that I don't use the loop, and the (DimACell) is actually a cell number "F5". I am assigning on the fly here because the variables being used determine whether I use cell "D5" of cell "F5". And this part seems to be working fine, in that it chooses the correct cell to work with.

What happens is that when the program is over, instead of having a Task manager clear of Excel instances, I end up with one still open. And each time this thing runs, which can be 50-60 times a day, it creates and keeps open another instance of Excel.

I have mainly just copied and pasted my other code which thanks to great assistance (and patience) from kevintheinstructor and others herein, I finally nailed down how to get all RCW's eliminated so when Excel closes down, it's all gone. Oh yes, here is the code I'm using to close down Excel when the code is completed.

        '******************************************************************************
        ' NOW SHUT DOWN EXCEL 
        '******************************************************************************
        vExcelWorkbook.Close(False)
        vExcelApp.UserControl = True
        vExcelApp.Quit()

        Marshal.FinalReleaseComObject(vExcelRange1)
        Marshal.FinalReleaseComObject(vExcelSheet2)
        Marshal.FinalReleaseComObject(vExcelSheet1)
        Marshal.FinalReleaseComObject(vExcelWorkbook)
        Marshal.FinalReleaseComObject(vExcelWorkbooks)
        Marshal.FinalReleaseComObject(vExcelApp)

        vExcelRange1 = Nothing
        vExcelApp = Nothing
        vExcelWorkbooks = Nothing
        vExcelWorkbook = Nothing
        vExcelSheet1 = Nothing
        vExcelSheet2 = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

I should add here that this project is basically a TCP Listener which waits until it receives a stream of data over the Internet from a commercial server at our hosting company. It processes that data and sends back the resulting calculations. The Excel portion of this project is simply another module within the project. I'm wondering if I should make two separate projects with the Excel being in it's own project. When I stop the TCP Listening process I check to see if the Excel instance is still in the task manager and it is. I have stepped through this code one line at a time and I can see that it definitely does go through all the lines which should close down Excel.

You know, I think I'll start looking into writing something which will count and track all the RCW's which get opened and when and if they get released. I could really use a tool like that now to find out which one if any of these is causing the trouble.

[kareninstructor]

Not that I will have a direct section of code to provide but instead reference back to things I said last time, got to be mindful of the "Two Dot Rule" and looping thru cells.

I just wrote about this yesterday here, timestamped Tuesday, April 09, 2013 3:14 PM

Issue with the Two Dot Rule and also specifying the sheet name (believe it or not), refer to my demos.

  vExcelWorkbook = vExcelApp.Workbooks.Open(vExcelFile)
  vExcelSheet1 = CType(vExcelWorkbook.Sheets("Sheet1"), Excel.Worksheet)
  vExcelSheet2 = CType(vExcelWorkbook.Sheets("Sheet2"), Excel.Worksheet)

Sniplet from my MSDN article, defensively assign a sheet to a variable

For x As Integer = 1 To xlWorkSheets.Count
    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

    If xlWorkSheet.Name = SheetName Then
        Proceed = True
        Exit For
    End If

    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
    xlWorkSheet = Nothing

Next

Bet this one is an issue too

   Do Until vExcelSheet1.Range("K6").Value >= vWord(1)
        vExcelRange1 = vExcelSheet1.Range(DimACell)
        vExcelRange1.Value = DimA
        DimA = DimA + vIncrement
   Loop

Also from my MSDN article

Dim Cells As String() = {"B2", "B3", "B4"}
For Each cell In Cells
    xlCells = xlWorkSheet.Range(cell)
    sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value))
    ReleaseComObject(xlCells)
Next

ReleaseComObject

Public Sub ReleaseComObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

[Vladamir]

Kevin,

Thanks again. I'm trying. I need to let you know that the loop I'm using is working on a more simplified version of this and it's getting rid of the Excel instances...all of them. I've switched over (due to design changes from the sales team) to a P2P method which transmit the data stream now...in lieu of an e-mail or web service which are running successfully now. I know, I know, if it ain't broke, don't fix it, but this has to happen. We can't keep these methods due to some new requirements of the team. So the P2P does work nice, it's just when I try to mix it in with the Excel process that the problems appear. I'm still working at this and will post some more information tomorrow...if I'm still alive.

[kareninstructor]

Just curious, when I see "data stream", are you using JSON ?

[Vladamir]

Just curious, when I see "data stream", are you using JSON ?

Not on this one. I'm just using the System.Net.Sockets to pickup the stream as it comes in on the defined port. I realize it might be kind of primitive but at this stage of the project I'm just getting it working. The big hosses around here are pleased so far and perhaps soon they will hire a real developer and let me get back to my main assignment which is LISP programming to automate AutoCAD.