Excel Workbooks are not being completely Closed via VB.net

[Papa_Don]

Hi group,

I'm discovering that my Excel spreadsheets that are being created by a VB.net app I've written isn't completely closing them as they should be. I'm struggling to understand why and how to fix this. Here is the code for the portion of the app that creates the workbook, inserts the data and closes the workbook (or it should be). Do you see some commands that I may be missing (they are at the bottom of the code)?

        xlApp = New Excel.Application
        xlApp.Visible = False
        ' Add a new workbook.
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Worksheet)

        If System.IO.File.Exists(DASRPTFileName) = True Then
            System.IO.File.Delete(DASRPTFileName)
        End If
        rowXl = 1
        Dim DASDay As String = ""
        Dim DASMonth As String = ""
        Dim DASYear As String = ""
        Dim DASDateTest As String = ""
        Dim DASDateSerial As Double = 0
        Dim DASDate As Date
        Dim propID As String

        Dim headers() As String = {"Prop No/Date", "Non-Guaranteed", "Guaranteed", "Stayovers", "Departures", "Adult", "Youth", "Children", "Sold", "OOO", "Off", "Not Sold", "Occ %", "Proj Revenue", "Avg Rate"}
        For col As Integer = 0 To UBound(headers)
            xlWorkSheet.Cells(1, col + 1) = headers(col)
        Next

        xlRange = xlWorkSheet.Range("A1", "O1")
        xlRange.WrapText = True
        xlRange.Interior.Color = Color.LightGray
        xlRange.HorizontalAlignment = -4108
        rowXl = rowXl + 1

        Dim objReader3 As New System.IO.StreamReader(DASRPT14Day)
        Do While objReader3.Peek() <> -1
            txtLine = objReader3.ReadLine()
            ' first convert date into Month, Day and Year (ex June 1, 2016)
            propID = Trim(Microsoft.VisualBasic.Left(txtLine, 5))
            DASDay = Microsoft.VisualBasic.Mid(txtLine, 7, 2)
            DASMonth = Microsoft.VisualBasic.Mid(txtLine, 10, 3)
            DASYear = Microsoft.VisualBasic.Mid(txtLine, 14, 2)
            DASDateTest = DASMonth & " " & DASDay & ", 20" & DASYear
            If IsNumeric(DASDay) = True Then
                ' converting the above date into a date number that Excel can read
                DASDate = CDate(DASDateTest)
                DASDateSerial = (Date.Parse(DASDateTest).ToOADate)
                If DASDateSerial < advDateSerial Then
                    ' combining the property number with the Excel date to create the field "propAndDate"
                    propAndDate = propID & "-" & DASDateSerial
                    nonGTD = CInt(Microsoft.VisualBasic.Mid(txtLine, 21, 8))
                    GTD = CInt(Microsoft.VisualBasic.Mid(txtLine, 30, 5))
                    stayovers = CInt(Microsoft.VisualBasic.Mid(txtLine, 36, 5))
                    departures = CInt(Microsoft.VisualBasic.Mid(txtLine, 42, 6))
                    adults = CInt(Microsoft.VisualBasic.Mid(txtLine, 49, 4))
                    youth = CInt(Microsoft.VisualBasic.Mid(txtLine, 54, 4))
                    children = CInt(Microsoft.VisualBasic.Mid(txtLine, 59, 4))
                    sold = CInt(Microsoft.VisualBasic.Mid(txtLine, 64, 5))
                    ooo = CInt(Microsoft.VisualBasic.Mid(txtLine, 70, 5))
                    off = CInt(Microsoft.VisualBasic.Mid(txtLine, 76, 5))
                    notSold = CInt(Microsoft.VisualBasic.Mid(txtLine, 82, 5))
                    occPcnt = CDbl(Microsoft.VisualBasic.Mid(txtLine, 88, 10))
                    revenue = CDbl(Microsoft.VisualBasic.Mid(txtLine, 99, 14))
                    avgRate = CDbl(Microsoft.VisualBasic.Mid(txtLine, 114, 13))

                    xlWorkSheet.Cells(rowXl, 1) = propAndDate
                    xlWorkSheet.Cells(rowXl, 2) = nonGTD
                    xlWorkSheet.Cells(rowXl, 3) = GTD
                    xlWorkSheet.Cells(rowXl, 4) = stayovers
                    xlWorkSheet.Cells(rowXl, 5) = departures
                    xlWorkSheet.Cells(rowXl, 6) = adults
                    xlWorkSheet.Cells(rowXl, 7) = youth
                    xlWorkSheet.Cells(rowXl, 8) = children
                    xlWorkSheet.Cells(rowXl, 9) = sold
                    xlWorkSheet.Cells(rowXl, 10) = ooo
                    xlWorkSheet.Cells(rowXl, 11) = off
                    xlWorkSheet.Cells(rowXl, 12) = notSold
                    xlWorkSheet.Cells(rowXl, 13) = occPcnt
                    xlWorkSheet.Cells(rowXl, 14) = revenue
                    xlWorkSheet.Cells(rowXl, 15) = avgRate
                    rowXl = rowXl + 1
                End If
            End If
        Loop
        xlRange = xlWorkSheet.Range("A:C")
        xlRange.ColumnWidth = 11.71
        xlRange = xlWorkSheet.Range("D:E")
        xlRange.ColumnWidth = 10.43
        xlRange = xlWorkSheet.Range("F:I")
        xlRange.ColumnWidth = 8.71
        xlRange = xlWorkSheet.Range("J:L")
        xlRange.ColumnWidth = 6.86
        xlRange = xlWorkSheet.Range("M:O")
        xlRange.ColumnWidth = 10.29
        xlRange.NumberFormat = "#,##0.00"

        xlApp.DisplayAlerts = False
        xlWorkBook.SaveAs(DASRPTFileName, FileFormat:=51, CreateBackup:=False)
        xlWorkBook.Close()
        xlApp.DisplayAlerts = True
        xlRange = Nothing
        xlWorkSheet = Nothing
        xlWorkBook = Nothing
        xlApp.Quit()
        xlApp = Nothing
        objReader3.Close()
        If System.IO.File.Exists(DASRPT14Day) = True Then
            System.IO.File.Delete(DASRPT14Day)
        End If

I'm hoping you see something I've overlooked. In advance, thanks for your help.

Don

[rproffitt]

This is a long shot but I wonder if the Personal.xlsb is in play. See the idea on that at:
http://superuser.com/questions/122926/how-do-i-get-excel-to-close-completely-after-creating-a-macro-in-a-personal-work

[Reverend Jim]

You could be a victim of the double dot problem. See the note section of this code snippet for details.

[PerplexedB]

This is definitely a double dot problem. You need to wrap the Excel object and implement the IDisposable interface in the wrapper. You also need to release the each Excel.Object you create. A minima you need the code I'm exposing below.
`

Imports Microsoft.Office.Interop
Public Class clsExcel
    Implements IDisposable
    Public oApp As New Excel.Application
    Public oWBs As Excel.Workbooks
    Public oWB As Excel.Workbook
    Public Selection As Excel.Range

    Public originalCulture As System.Globalization.CultureInfo
    Dim thisThread As System.Threading.Thread
    Dim lQuit As Boolean = True
    Dim lDisposed As Boolean = False

    Public cPrinter As String
    Public nCopies As Integer
    Public cXlsRoot As String
    Sub New()
        Me.thisThread = System.Threading.Thread.CurrentThread
        Me.originalCulture = thisThread.CurrentCulture
        Me.thisThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
        Me.oWBs = Me.oApp.Workbooks
        Me.oApp.DisplayAlerts = False
    End Sub
    Sub Dispose() Implements IDisposable.Dispose
        If Me.lDisposed Then Exit Sub
        If lQuit Then Me.oApp.Quit()
        Me.releaseObject(Me.oWB)
        Me.releaseObject(Me.oWBs)
        Me.releaseObject(Me.oApp)
        Me.thisThread.CurrentCulture = Me.originalCulture
        Me.lDisposed = True
    End Sub
    Protected Overrides Sub finalize()
        Me.Dispose()
    End Sub
    Public Sub releaseObject(ByVal o As Object)
        If o Is Nothing Then Exit Sub
        Runtime.InteropServices.Marshal.FinalReleaseComObject(o)
        o = Nothing
    End Sub
End Define

`
Please be forewarned that his approach is not advisable. You might want to use Excel without using Interop, i.e. generating the xls(x) directly without access to Excel. There must be packages available, let me know (I'm still using this and getting more and more problems with it).

[Papa_Don]

@ Reverend Jim,

I've gone back to working on this with no success. I've done a lot of reading and all offer various ideas. I've also tried your method of closing the instances with no success. Here is my latest updates:

        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
        ' Start Excel and get Application object.
        Dim xlApp As New Excel.Application
        xlApp.Visible = False
        ' Add a new workbook.
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Worksheet)
        ' Run the rest of the routine

        'Closing all the instances of Excel:
        xlApp.DisplayAlerts = False
            xlWorkBook.SaveAs(newExlName, FileFormat:=51, CreateBackup:=False)
            xlWorkBook.SaveAs(stdExlName, FileFormat:=51, CreateBackup:=False)
            xlApp.DisplayAlerts = True
            xlWorkBook.Close(newExlName)
            xlWorkBook.Close(stdExlName)
            xlApp.Quit()
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)
            xlRange = Nothing
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
End Sub

Private Sub releaseObject(ByVal obj As Object)
        'Release an automation object
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
End Sub

I cannot get these instances of Excel to close after numerous changes and attempts. Do you or any others have some good ideas? Are there some commands to kill any open Excel process that is open?

Needless to say, with all of these Excel instances open, it really slows my computer down. Any help will be appreciated.

Don

[rproffitt]

@doncwilson. When I read your top post it was "Excel Workbooks are not being completely Closed via VB.net" and now
"I cannot get these instances of Excel to close after numerous changes and attempts".

So that's different so as a test, do these processes exit/close when your app or if running in Visual Studio (which version?) closes?

Why I ask is in one app that I fielded I had to spawn a copy of my app to do a thing and it would exit when done and that would clean up nicely. Files closing is one thing, a process is another. Sorry that I didn't read that right in your opening post.

[Papa_Don]

@rproffitt, To clarify, because as the app ran I noticed the computer speed degrading. So I opened the task manager and saw many many instances of Excel open (via the "Processes" tab. That's when I knew Excel wasn't completely closing the process. This tool was built in Visual Studio 2015.

If you have ideas, don't hesitate to offer it. I'll try anything to get these processes to shut down.

Thanks for your interest.

Don

[rproffitt]

@Don. I didn't find an answer to my question. That test would have me offer the method I used in another app where I launch workers to do the thing then workers die as they finish. The main app manages how many workers are running. Since we have multicore machines now, this is how I maximized my old app throughput.

[Minimalist]

The sledgehammer method would be to close all excel processes. This can be done with:

  Dim obj1(1) As Process
            obj1 = Process.GetProcessesByName("EXCEL")
            For Each p As Process In obj1
                    p.Kill()
                Next

However, if others are also using excel , their processes will also be killed.
Reference the this discussion:
http://stackoverflow.com/questions/11761379/excel-process-still-runs-after-closing-in-vb-net

[Papa_Don]

@Minimalist, when you say "if others are also using excel , their processes will also be killed.", are you meaning that if the user has other instances of Excel open, those will be killed too? Or do you mean other users on our network will have their instances of Excel killed?

The latter doesn't seem right, but I can see where any instance of excel could be killed if others are open.

I did read through the link earlier today but didn't attempt anything. If it's only going to kill all the Excel processes on my computer, I'm good with that.

Don

[Minimalist]

Well, I am not on a network so I can't test if all instance of excel are going to be closed. However testing on my machine shows that: if you open more than one instance of excel through vb.net of directly starting excell all instances will be killed. To work out how many instance of excel your machine can see just convert the code I posted to:

Dim obj1(1) As Process
        Dim counter As Integer = 0
        obj1 = Process.GetProcessesByName("EXCEL")
        For Each p As Process In obj1
            counter += 1
        Next
        Debug.Print(counter.ToString)
    End Sub

If you don't run an instance of excel the counter should be 0 which means also that either your machine is not running excel and no one else is running excel, hence you can kill all instances of excel. If you however not running excel and the counter >0 you need to find a way to only kill your instances of excel and there the process id comes in. Follow the link I posted.

[Minimalist]

@ ddanbe and @ rproffitt
Thanks guys.

[Papa_Don]

@ Minimalist, I've run your test and found that it didn't find or kill anything on the network. It only killed those Excel applications running on my computer (which is what I anticipated and wanted it to do). Ironically though, I had to run the "Kill" commands twice to get it to clear everything out.

Thanks to everyone who helped out. This was truly great learning - which I greatly appreciate!

Don