[RESOLVED] Excel won't quit

[dethredic]

So I have a little gui that will read a given spreadsheet and list all the "valid" sheets. This happens when a user presses a button.

here is my code:

Private Sub bttnScanWorksheets_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnScanWorksheets.Click
        If System.IO.File.Exists(TextBox1.Text) = True Then
 
            'Clear checkbox area
            CheckedListBox1.Items.Clear()
 
            ' Open excel
            Dim excelapp As Excel.Application
            Dim WB As Excel.Workbook
            Dim WS As Excel.Worksheet
            excelapp = New Excel.Application
            WB = excelapp.Workbooks.Open(TextBox1.Text)
            Dim CheckValid As Boolean
            Dim SheetsAdded As Boolean = False
 
            ' Cycle through each sheet
            For Each WS In WB.Worksheets
                Try
                    CheckValid = True
                    If WS.Range("B4").Value.ToString <> "Employee Name:" Then
                        CheckValid = False
                    End If
                    If WS.Range("H4").Value.ToString <> "Week No.:" Then
                        CheckValid = False
                    End If
                    If WS.Range("F8").Value.ToString <> "Cost" Then
                        CheckValid = False
                    End If
 
                    ' Add the sheet to the list
                    If CheckValid = True Then
                        CheckedListBox1.Items.Add(WS.Name) ' Populate the checkbox area
                        SheetsAdded = True
                    End If
                Catch ex As NullReferenceException ' Catch errors that arrise from merged cells.
                Catch ex As Exception
                    MsgBox(ex.ToString) ' any other errors will be caught here
                End Try
            Next
 
            If SheetsAdded = False Then
                MsgBox("There were no valid sheets in this workbook.")
            End If
 
            ' Close Excel
            WB.Close()
            excelapp.Quit()
            Marshal.ReleaseComObject(excelapp)
            excelapp = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
        Else
            MsgBox("That file does not exist")
        End If
    End Sub

The problem is that Excel.exe doesn't quit until the program closes for some reason. This causes the spreadsheet to remain read only until the program is closed.

Any ideas why excel isn't closing properly?

[HanneSThEGreaT]

Try something like this - you almost had it :

xlbook.Close 'close worbook
'...do the same as the following for range references if any, and all the excel stuff you've used...
.Marshal.ReleaseComObject(ws) 'the worksheet
.InteropServices.Marshal.ReleaseComObject(xlbook) 'the workbook

xlapp.Quit() 'Then quit

.Marshal.ReleaseComObject(xlapp) 'then release the application

[dethredic]

Ok, I changed it to this:

' Close Excel
            WB.Close()            
            Marshal.ReleaseComObject(WB)
            excelapp.Quit()
            'excelapp = Nothing
            Marshal.ReleaseComObject(excelapp)
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()

same problem. Excel closes, but only when I exit the program.

edit: I have the same closing method in another function, which works how I would expect it too.

[si_the_geek]

You have missed out WS.

[dethredic]

You have missed out WS.

If I add:

WS.Close() above WB.Close() then I get a "MissingMemberException was unhanded"

[si_the_geek]

That is to be expected, because the worksheet object does not have a method with that name (or any method which serves that purpose).

Try what HanneSThEGreaT suggested:

.Marshal.ReleaseComObject(ws) 'the worksheet

[dethredic]

That does not help either.

I am starting to think I am closing it right, but there is something else that is causing it to stay open because it closes fine in another function.

[dethredic]

any other ideas?

[cicatrix]

Have you monitored with Task Manager whether excel.exe process actually closes after excelapp.Quit() and how many excel.exe instances do you have? Does your workbook contain some other embedded com objects or OLE links? Try it with an empty workbook.

[gautamshaw]

you may try this:

   Public Sub releaseobject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
        GC.Collect()
    End Sub
    
    'after closing the workbook add this
    apl.Quit()

        releaseobject(apl)
        releaseobject(wrkbk)
        releaseobject(wrksht)

[dethredic]

Have you monitored with Task Manager whether excel.exe process actually closes after excelapp.Quit() and how many excel.exe instances do you have? Does your workbook contain some other embedded com objects or OLE links? Try it with an empty workbook.

I did monitor the task manager. As soon as I hit my button an excel.exe appears. I then hit another button which opens up the same spread sheet and does similar stuff. Another excel.exe pops up, but then goes away (it closes like I have shown in my original post). I then hit the first button again and no new excel.exe is added (I am not sure if it closes and opens again really fast or not)

I then tried choosing a different workbook, and "invalid one". I got the msgbox: MsgBox("There were no valid sheets in this workbook."). Another excel.exe got opened. I clicked "OK" and they both closed (hurray?).

Finally I started a new workbook and filled in the 3 required cells. I then tried that and no excel.exe got opened!

I am now super confused how it closes in another function and if it isn't valid, but doesn't if this function.


@ gautamshaw: thanks but that didn't help

[si_the_geek]

I don't do much work in VB.Net, but based on lots of previous work with Excel from VB6 I recommend using gautamshaw's releaseobject in this order:

            releaseobject(WS)
            WB.Close()
            releaseobject(WB)
            excelapp.Quit()
            releaseobject(excelapp)

If that doesn't fix it, try moving the first line to just before the end of the For loop.

[dethredic]

I don't do much work in VB.Net, but based on lots of previous work with Excel from VB6 I recommend using gautamshaw's releaseobject in this order:

            releaseobject(WS)
            WB.Close()
            releaseobject(WB)
            excelapp.Quit()
            releaseobject(excelapp)

If that doesn't fix it, try moving the first line to just before the end of the For loop.

Again this didn't work. I don't think it is a problem with how I am closing it (because it works other places), but something else must be the problem, or maybe the workbook itself because the close worked on other workbooks.

[dethredic]

Ok, I have done some rigorous testing and have some new info.

If the database is empty then excel quits as expected.
If the database is not empty then excel does not quit until the program exits.

code:
using the release object function from above.

Function PopulateCheckBoxArea()
        If System.IO.File.Exists(TextBox1.Text) = True Then
 
            'Clear checkbox area
            CheckedListBox1.Items.Clear()
 
            ' Open excel
            Dim excelapp As Excel.Application
            Dim WB As Excel.Workbook
            Dim WS As Excel.Worksheet
            excelapp = New Excel.Application
            WB = excelapp.Workbooks.Open(TextBox1.Text)
            Dim CheckValid As Boolean
            Dim SheetsAdded As Boolean = False
 
            ' Cycle through each sheet
            For Each WS In WB.Worksheets
                Try
                    CheckValid = True
                    If WS.Range("B4").Value.ToString <> "Employee Name:" Then
                        CheckValid = False
                    End If
                    If WS.Range("H4").Value.ToString <> "Week No.:" Then
                        CheckValid = False
                    End If
                    If WS.Range("F8").Value.ToString <> "Cost" Then
                        CheckValid = False
                    End If
 
                    ' Check to see if the sheet has already been added to the database
                    If CheckValid = True Then
 
                        ' Get name of employee from spreadsheet
                        Dim aName As Array
                        aName = Split(WS.Range("C4").Value, " ")
                        Dim nameexists = False
                        Dim sheetadded = False
 
                        ' Check if the Employee is already in the Database
                        Dim currentemployeeID As Int32
                        Dim aEmployeeID As Int32
 
                        Using connection As New SqlConnection(ConnectionString)
                            Using command As New SqlCommand("SELECT NameFirst, NameLast, EmployeeID FROM Employees", _
                                                          connection)
                                connection.Open()
                                Using reader As SqlDataReader = command.ExecuteReader()
                                    While reader.Read()
                                        currentemployeeID = reader("EmployeeID")
                                        If aName(0) = RemoveWhiteSpace(reader("NameFirst")) Then
                                            If aName(1) = RemoveWhiteSpace(reader("NameLast")) Then
                                                ' The name exists in the database, get the EmployeeID to match the name
                                                aEmployeeID = currentemployeeID
                                                nameexists = True
                                            End If
                                        End If
                                    End While
                                End Using
                            End Using
                        End Using
 
 
                        ''''' Timesheet Table ''''''
                        If nameexists = True Then
 
                            Dim aWeekNumber As Int32 = WS.Range("I4").Value
 
                            Using connection As New SqlConnection(ConnectionString)
                                Using command As New SqlCommand("SELECT TimesheetID, EmployeeID, WeekNumber FROM Timesheet", _
                                                              connection)
                                    connection.Open()
                                    Using reader As SqlDataReader = command.ExecuteReader()
                                        While reader.Read()
                                            If aWeekNumber = RemoveWhiteSpace(reader("WeekNumber")) Then
                                                If aEmployeeID = RemoveWhiteSpace(reader("EmployeeID")) Then
                                                    ' The Timesheet has already been added
                                                    sheetadded = True
                                                End If
                                            End If
                                        End While
                                    End Using
                                End Using
                            End Using
                        End If
 
                        ' Add the sheet to the list
                        If sheetadded = False Then
                            CheckedListBox1.Items.Add(WS.Name) ' Populate the checkbox area
                            SheetsAdded = True
                        End If
 
                    End If
                Catch ex As NullReferenceException ' Catch errors that arrise from merged cells.
                Catch ex As Exception
                    MsgBox(ex.ToString) ' any other errors will be caught here
                End Try
                Marshal.ReleaseComObject(WS)
            Next
 
 
            ' Close Excel
            WB.Close()
            Marshal.ReleaseComObject(WB)
            excelapp.Quit()
            Marshal.ReleaseComObject(excelapp)
            excelapp = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
 
            If SheetsAdded = False Then
                MsgBox("There were no valid sheets in this workbook.")
            End If
 
        Else
            MsgBox("That file does not exist")
        End If
 
    End Function

[si_the_geek]

There are only three things there that seem like they could be potential issues... the first is the RemoveWhiteSpace function (which I presume has nothing to do with Excel, and thus is irrelevant), and the other two are these two lines of code:

                            Dim aWeekNumber As Int32 = WS.Range("I4").Value
                            CheckedListBox1.Items.Add(WS.Name) ' Populate the checkbox area

Try altering each of those so they don't refer to Excel... hopefully you'll find that one of them is the culprit.

[dethredic]

There are only three things there that seem like they could be potential issues... the first is the RemoveWhiteSpace function (which I presume has nothing to do with Excel, and thus is irrelevant), and the other two are these two lines of code:

                            Dim aWeekNumber As Int32 = WS.Range("I4").Value
                            CheckedListBox1.Items.Add(WS.Name) ' Populate the checkbox area

Try altering each of those so they don't refer to Excel... hopefully you'll find that one of them is the culprit.

Dim aWeekNumber As Int32 = WS.Range("I4").Value was the problem.

Thanks so much.

[si_the_geek]

Excellent... I'm glad we got there at last

For the sake of anyone else who has a similar problem later, can you show the 'corrected' version of that line?

[dethredic]

I took it out and just use the value directly (without declaring it)

''''' Timesheet Table ''''''
                        If nameexists = True Then
 
                            Using connection As New SqlConnection(ConnectionString)
                                Using command As New SqlCommand("SELECT TimesheetID, EmployeeID, WeekNumber FROM Timesheet", _
                                                              connection)
                                    connection.Open()
                                    Using reader As SqlDataReader = command.ExecuteReader()
                                        While reader.Read()
                                           'change was made below
                                            ' If aWeekNumber = RemoveWhiteSpace(reader("WeekNumber")) Then
                                            If WS.Range("I4").Value = RemoveWhiteSpace(reader("WeekNumber")) Then
                                                If aEmployeeID = RemoveWhiteSpace(reader("EmployeeID")) Then
                                                    ' The Timesheet has already been added
                                                    sheetadded = True
                                                End If
                                            End If
                                        End While
                                    End Using
                                End Using
                            End Using
                        End If