How to quit excel application from VB.NET

Question

My program is able to retrieve data from an excel macro 2010 workbook and change contents and save changes made, all using the datagridview within VB.NET. However I'm facing a problem where the program saves but will not close. When I look at the processes in the task manager its still showing Excel 2010 as running. If anyone can help me find a way to quit this application I would greatly appreciate it!

Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.IO

Public Class Form1
Dim SheetList As New ArrayList
Private excelObj As ExcelObject
Private dt As DataTable = Nothing
Dim DS As DataSet
Dim DS2 As DataSet
Dim ds3 As DataSet
Dim ds4 As DataSet
Dim ds5 As DataSet
Dim ds6 As DataSet
Dim ds7 As DataSet
Dim ds8 As DataSet
Dim ds9 As DataSet
Dim ds10 As DataSet
Dim ds11 As DataSet
Dim ds12 As DataSet
Dim ds13 As DataSet
Dim ds14 As DataSet
Dim ds15 As DataSet
Dim ds16 As DataSet
Dim ds17 As DataSet
Dim ds18 As DataSet

Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyCommand2 As OleDb.OleDbDataAdapter
Dim MyCommand3 As OleDb.OleDbDataAdapter
Dim MyCommand4 As OleDb.OleDbDataAdapter
Dim MyCommand5 As OleDb.OleDbDataAdapter
Dim MyCommand6 As OleDb.OleDbDataAdapter
Dim MyCommand7 As OleDb.OleDbDataAdapter
Dim MyCommand8 As OleDb.OleDbDataAdapter
Dim MyCommand9 As OleDb.OleDbDataAdapter
Dim MyCommand10 As OleDb.OleDbDataAdapter
Dim MyCommand11 As OleDb.OleDbDataAdapter
Dim MyCommand12 As OleDb.OleDbDataAdapter
Dim MyCommand13 As OleDb.OleDbDataAdapter
Dim MyCommand14 As OleDb.OleDbDataAdapter
Dim MyCommand15 As OleDb.OleDbDataAdapter
Dim MyCommand16 As OleDb.OleDbDataAdapter
Dim MyCommand17 As OleDb.OleDbDataAdapter
Dim MyCommand18 As OleDb.OleDbDataAdapter

Dim objExcel As New Excel.Application()
Dim objWorkBook As Excel.Workbook = objExcel.Workbooks.Add
Dim objWorkSheet1 As Excel.Worksheet = objExcel.ActiveSheet
Dim objWorkSheet2 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet3 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet10 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet11 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet12 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet13 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet23 As Excel.Worksheet = objExcel.ActiveSheet
Dim objworksheet24 As Excel.Worksheet = objExcel.ActiveSheet
'<TBD make 15 more of these>
Dim MyConnection As OleDb.OleDbConnection

Dim MYDBConnection As DAO.Connection
Public MyWorkspace As DAO.Workspace
Public sizetable As DAO.Recordset
Public MyDatabase As DAO.Database


Public ReadOnly Property Excel() As ExcelObject
    Get
        If excelObj Is Nothing Then
            excelObj = New ExcelObject(txtFilePath.Text)
        End If
        Return excelObj
    End Get
End Property


Sub openExcelfile()


    Dim dlg As New OpenFileDialog()
    dlg.Filter = "Excel Macro Enabled Files|*.xlsm*|Excel Files|*.xls|Excel 2007 Files|*.xlsx|All Files|*.*"
    If dlg.ShowDialog() = DialogResult.OK Then
        excelObj = New ExcelObject(dlg.FileName)
        txtFilePath.Text = dlg.FileName
        btnRetrieve.Enabled = txtFilePath.Text.Length > 0
        End If




        Dim ExcelSheetName As String = ""
        'open the excel workbook and create an object for it
        objExcel = CreateObject("Excel.Application")
        'do some exception handling on a blank txtfilepath.text

        objWorkBook = objExcel.Workbooks.Open(txtFilePath.Text)

        Dim i As Integer
        i = 1
        For Each objWorkSheets In objWorkBook.Worksheets
            SheetList.Add(objWorkSheets.Name)
            Select Case i
                Case 4

                    objWorkSheet1 = objExcel.Worksheets(objWorkSheets.Name)

                Case 5
                    objWorkSheet2 = objExcel.Worksheets(objWorkSheets.Name)
                    'ListBox1.Items.Add(objWorkSheets.Name)
                'etc
            End Select
            i = i + 1

        Next

End Sub

Sub Write2Excel()

    Dim rowindex As Integer
    Dim columnindex As Integer

    For rowindex = 1 To DataGridView1.RowCount
        For columnindex = 1 To DataGridView1.ColumnCount
            objWorkSheet1.Cells(rowindex + 4, columnindex + 0) = DataGridView1(columnindex - 1, rowindex - 1).Value

        Next
    Next

    'etc

End Sub


Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
    openExcelfile()

End Sub
Sub oldretrieve()
    '    Dim dt As DataTable = Me.Excel.GetSchema()
    '   cmbTableName.DataSource = (From dr In dt.AsEnumerable() Where Not dr("TABLE_NAME").ToString().EndsWith("$") Select dr("TABLE_NAME")).ToList()
    '  cmbTableName.Enabled = cmbTableName.Items.Count > 0
    ' btnGo.Enabled = cmbTableName.Items.Count > 0
    '     btnDrop.Enabled = cmbTableName.Items.Count > 0
End Sub
Sub RetrieveExcel()

    'Create a connection to either 2007 and 2010 xls file
    Dim fi As New FileInfo(txtFilePath.Text)
    If fi.Extension.Equals(".xls") Then
        MyConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.8.0; " & "data source=" & txtFilePath.Text & "; " & "Extended Properties=Excel 8.0;")

    ElseIf fi.Extension.Equals(".xlsx") Then
        MyConnection = New OleDb.OleDbConnection( _
        "provider=Microsoft.Ace.OLEDB.12.0; " & _
        "data source=" & txtFilePath.Text & "; " & "Extended Properties=Excel 12.0;")
    ElseIf fi.Extension.Equals(".xlsm") Then
        MyConnection = New OleDb.OleDbConnection( _
        "provider=Microsoft.Ace.OLEDB.12.0; " & _
        "data source=" & txtFilePath.Text & "; " & "Extended Properties=Excel 12.0;")

    End If


   'First worksheet'
    MyCommand = New OleDbDataAdapter("select * from [1- COTS Worksheet$A4:I150]", MyConnection)
    '1- COTS Worksheet.Column(1).Locked = True
    DS = New System.Data.DataSet()
    MyCommand.Fill(DS)

    '---This will prevent the user from editing the size of the rows and columns of the datagrid---'
    DataGridView1.AllowUserToResizeColumns = False
    DataGridView1.AllowUserToResizeRows = False
    DataGridView1.AllowUserToOrderColumns = False
    DataGridView1.AllowUserToAddRows = False
    DataGridView1.AllowUserToDeleteRows = False

    DataGridView1.DataSource = DS.Tables(0).DefaultView
    '---The following line makes the column read only---'
    DataGridView1.Columns(5).ReadOnly = True
    DataGridView1.Columns(6).ReadOnly = True


    '''''''if the column is editible then the foreground = blue ''''''''
    DataGridView1.Columns(0).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(1).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(2).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(3).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(4).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(7).DefaultCellStyle.ForeColor = Color.Blue
    DataGridView1.Columns(8).DefaultCellStyle.ForeColor = Color.Blue

    ' ''''''This will get rid of the selection blue color for the cells''''''''''''''''''''
    DataGridView1.DefaultCellStyle.SelectionBackColor = DataGridView1.DefaultCellStyle.BackColor
    DataGridView1.DefaultCellStyle.SelectionForeColor = DataGridView1.DefaultCellStyle.ForeColor




    'TABLE TO WRITE TO -
    'FIELD TO WRITE TO -

End Sub


Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click
    Try
        Cursor.Current = Cursors.WaitCursor
        RetrieveExcel()
    Finally
        Cursor.Current = Cursors.Default
    End Try


End Sub




Private Sub Form1_Activated(sender As Object, e As EventArgs) Handles Me.Activated

End Sub

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    '  btnRetrieve.Enabled = True
    MyWorkspace = DAODBEngine_definst.Workspaces(0)
End Sub



Private Sub txtFilePath_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFilePath.TextChanged
    btnRetrieve.Enabled = System.IO.File.Exists(txtFilePath.Text)
End Sub





Private Sub Form1_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
    If Me.excelObj IsNot Nothing Then
        Me.excelObj.Dispose()

    End If
    closexlsfile()


End Sub

Private Sub DBFilePath_TextChanged(sender As Object, e As EventArgs) Handles DBFilePath.TextChanged

End Sub

Private Sub BtnBrowseDB_Click(sender As Object, e As EventArgs) Handles BtnBrowseDB.Click
    opendbfile()
End Sub



Function opendbfile() As Boolean

    Dim dlg As New OpenFileDialog()
    dlg.Filter = "DB files|*.mdb|Access DB files|*.accdb|All Files|*.*"
    If dlg.ShowDialog() = DialogResult.OK Then

        DBFilePath.Text = dlg.FileName
        'temporarily myfile will be set to c:/Exceltest/template.mdb
        '  myfile = "c:/Exceltest/template.mdb"
        Try
            If DBFilePath.Text <> "" Then

                'On Error GoTo errorhandler
                MYDBConnection = MyWorkspace.OpenConnection("provider=Microsoft.Ace.OLEDB.12.0; " & "data source=" & txtFilePath.Text)
                MyDatabase = MyWorkspace.OpenDatabase(DBFilePath.Text)
                sizetable = MyDatabase.OpenRecordset("size", DAO.RecordsetTypeEnum.dbOpenTable)

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End If

End Function



Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click
    '---This Try Finally block with set current cursor to waiting cursor while the program write to excel---'
    Try
        Cursor.Current = Cursors.WaitCursor
        Write2Excel()
    Finally
        Cursor.Current = Cursors.Default
    End Try

End Sub


Sub closexlsfile()
    Try
        ''Do we need to save the file first?
        objWorkBook.Save()
        objWorkBook.Close()
        objExcel.Quit()
        'something weird happening on this line
        MyConnection.Close()
        MyConnection.Dispose()
        objWorkBook = Nothing
        objExcel = Nothing

    Catch
    End Try

    'TBD the other objworksheets get closed here


End Sub
Private Sub closexls_Click(sender As Object, e As EventArgs) Handles closexls.Click
    closexlsfile()
    NAR(objWorkSheet1)
    NAR(objWorkSheet2)
    NAR(objworksheet3)
    NAR(objworksheet10)
    NAR(objworksheet11)
    NAR(objworksheet12)
    NAR(objworksheet13)
    NAR(objworksheet23)
    NAR(objworksheet24)
    objWorkBook.Close(False)
    NAR(objWorkBook)
    NAR(MyConnection)
    objExcel.Quit()
    NAR(objExcel)

    Debug.WriteLine("Sleeping...")
    System.Threading.Thread.Sleep(5000)
    Debug.WriteLine("End Excel")

End Sub

'---This is a method that I found of MSDN to quit an office application but it doesn't seem to work---'
Private Sub NAR(ByVal obj As Object)
    Try
        While (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0)

        End While
    Catch
    Finally
        obj = Nothing

    End Try
End Sub



End Class

http://stackoverflow.com/q/158706/293078. And here's more about the two-dot rule: http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/ — Doug Glancy, Jun 07 '13 at 18:31
-1 for posting so much irrelevant code. Narrow the problem down first! — John Saunders, Jun 07 '13 at 18:31
It sucks to get downvoted. It happened to me my first question, too. But John is right. As well as helping the Original Poster who asked the question, we have just an obligation the rest of the community, too. We want questions that benefit everyone here. It is less beneficial to everyone if they have to decode a giant class to understand the question and answer. — Katie Kilian, Jun 07 '13 at 19:00

score 1 · Answer 1 · answered Jun 07 '13 at 18:31

You obviously have a lot of code and I can't go through all of that. However I dealt with this problem in the past. It is usually due to an unreleased object of some kind. For example lots of code samples from the intertubes suggests that you do things like

objWorkBook = objExcel.Workbooks.Open(txtFilePath.Text)

This is potentially dangerous, you should never have more than ONE . in a single right hand value. Instead go for something like

Workbooks wrkbks = objExcel.Workbooks
objWorkBook = wrkbks.Open(txtFilePath.Text)

Otherwise there will be memory allocated for the WorkBooks that isn't explicitly released. This is really a pain to go through all your code base once you've discovered this, but it solved the problem for me.

score 0 · Answer 2 · answered Jun 07 '13 at 19:29

Your NAR sub should have it handled, but I far prefer Marshal.FinalReleaseComObject method - no loop required. You need to make sure all instances have been addressed - like in the closexlsfile() method you do not call NAR for these Com objects. I suggest some code cleanup.

How to quit excel application from VB.NET

2 Answers2