[RESOLVED] Closing Excel

Okay so my program opens excel, reads a few cells, and closes excel. But, it does not fully close excel and after multiple runs, I have multiple EXCEL.EXE *32 in my process list. It also asks for a save when it's closing when there are no changes being made, is there any way to avoid this? And how do you get Excel to fully close?

Imports Excel = Microsoft.Office.Interop.Excel

vb Code:

Public Sub XLMain()
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = xlApp.Workbooks.Open(strExcelPath)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
        'Stuffs to be did
        ReadXL()
        ImportXL()
        'Close Excel:
        CloseExcel()
    End Sub
    Public Sub CloseExcel()
        xlBook.Close()
        xlApp.Quit()
    End Sub
    Public Sub ReadXL()
        Try
            ordernumber = xlSheet.Cells(5, "C").Value.ToString
            owner1 = xlSheet.Cells(6, "C").Value.ToString
            county = xlSheet.Cells(6, "I").Value.ToString
            fulladdress = xlSheet.Cells(7, "C").Value.ToString
            address = Mid(fulladdress, 1, InStr(fulladdress, ",") - 1)
            halfaddress = Mid(fulladdress, address.Length + 2)
            zip = Mid(fulladdress, fulladdress.Length - 4, 5)
            city = Mid(halfaddress, 1, InStr(halfaddress, ",") - 1)
            state = Mid(fulladdress, address.Length + city.Length + 4, 2)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

I also just tried this, and it did not work:

vb Code:

Public Sub CloseExcel()
        'xlBook.Close()
        'xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        GC.Collect()
    End Sub

Just tried this also, and it did not work:

vb Code:

Dim strExcelPath As String = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
    Dim xlApp = New Excel.Application()
    Dim xlWbs = xlApp.Workbooks
    Dim xlBook = xlWbs.Open(strExcelPath)
    Dim xlSheet = xlBook.Worksheets(1)
    Public Sub CloseExcel()
        xlBook.Close()
        xlApp.Quit()
        xlBook = Nothing
        xlWbs = Nothing
        xlApp = Nothing
    End Sub

The program closes, without asking for a save, gets me the data I need, but remains in the process list.
Using this for my Imports

Code:

---

Imports Excel = Microsoft.Office.Interop.Excel

---

Hello,

Right from the start what is wrong, how the automation is done from creating an instance of an Excel app to how you are assigning the variables for workbook and worksheet. Any time you can count more than one "." in a line of code working with Excel automation you are causing an object not to release (under normal conditions) when the code finishes executing commonly known as tunnelling.

Code:

---

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = xlApp.Workbooks.Open(strExcelPath)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

---

The following example used as provided you should open Task Manager to the Process tab and notice that Excel appears while in use then disappears because the method used to close and dispose are done in a proper sequence and no tunnelling is done. Excel automation when done half way correct looks easy while done correctly requires a great deal of work to ensure every object is disposed of properly.

Requires an OpenDialog on the form this code is used. The default location is My Documents to find Excel files. Ignore the initial value for InitialPath variable.

Code:

---

Private Sub Button1_Click(
    ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles Button1.Click

    Dim InitialPath As String = "C:\DotnetLand2010\Excel\ExcelSoda\bin\Debug"

    OpenFileDialog1.Title = "Please select a file to open"
    OpenFileDialog1.FileName = ""

    If IO.Directory.Exists(InitialPath) Then
        OpenFileDialog1.InitialDirectory = InitialPath
    Else
        OpenFileDialog1.InitialDirectory =
            System.Environment.GetFolderPath(
                Environment.SpecialFolder.MyDocuments)
    End If

    OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"

    If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)

        xlApp.Visible = False

        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

        Dim SingleCellToRead = xlWorkSheet.Range("A1")
        Dim A1_Value As String = String.Format("A1 = '{0}'", SingleCellToRead.Value)

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        If Not SingleCellToRead Is Nothing Then
            Marshal.FinalReleaseComObject(SingleCellToRead)
            SingleCellToRead = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If

        MessageBox.Show(A1_Value)

    End If
End Sub

---

Okay this is what I have now and it is working, I could not get the Marshal command to work still.

vb Code:

Dim strExcelPath As String = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
    Dim xlApp = New Excel.Application()
    Dim xlWbs = xlApp.Workbooks
    Dim xlBook = xlWbs.Open(strExcelPath)
    Dim xlSheet = xlBook.Worksheets(1)
    Public Sub XLMain()
        ReadXL()
        ImportXL()
        'Close Excel:
        CloseExcel()
    End Sub
    Public Sub CloseExcel()
        xlBook.Close()
        xlApp.Quit()
        KillProcess()
        xlBook = Nothing
        xlWbs = Nothing
        xlApp = Nothing
    End Sub
    Public Sub KillProcess()
        Dim proc = Process.GetProcessesByName("EXCEL.EXE")
        For i As Integer = 0 To proc.Count - 1
            proc(i).CloseMainWindow()
        Next i
    End Sub

Is this a good way to do this? It is working, but I'm not sure if this is the best way or if it will work every time. It does not close Excel until the Test Form is closed, this is going to be a Class Library, will it close when it is called in this case? The .DLL will be running on a windows service 24/7

Basically, Excel is still in the process list until the Form is closed now rather than when the KillProcess is called, if this is a class library instead of a form, will it do the same thing?

What do you mean by not getting Marshal.FinalReleaseComObject to work?

Also the following will cause an issue with disposal

Code:

---

Dim xlSheet = xlBook.Worksheets(1)

---

If you need to select a worksheet this way then add the following to the end of my example right before the MessageBox

Code:

---

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

---

There is never a need to use your KillProcess code unless your code crashes before completion because of a run time exception. Under normal conditions what I have provided works. Get back to me about the Marshal.FinalReleaseComObject not working.

I had System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) and Marshal.ReleaseComObject(xlApp) in my CloseExcel but neither of them removed it from the process list, I never had a problem actually "closing" it but rather removing it as a process, but that being true it was never closed in the first place, it was just never visible so I wouldn't know.

Code:

---

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

---

This fixed it, thanks.

vb Code:

Dim strExcelPath As String = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
    Dim xlApp = New Excel.Application()
    Dim xlWbs = xlApp.Workbooks
    Dim xlBook = xlWbs.Open(strExcelPath)
    Dim xlSheet = xlBook.Worksheets(1)    
    Public Sub CloseExcel()
        xlBook.Close()
        xlApp.Quit()
        xlApp = Nothing
        xlWbs = Nothing
        xlBook = Nothing
        xlSheet = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

See attached VS2010 project which uses the code I provided and also shows all open processes beginning with 'e' like Excel so you need not keep task manager open. Try CheckBox1 checked and unchecked (see comments inside)

Quote:

---

Originally Posted by thebuffalo

I had System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) and Marshal.ReleaseComObject(xlApp) in my CloseExcel but neither of them removed it from the process list, I never had a problem actually "closing" it but rather removing it as a process, but that being true it was never closed in the first place, it was just never visible so I wouldn't know.

Code:

---

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

---

This fixed it, thanks.

---

Cool!!! Make sure to look at the attached project in my reply before this one.

My excel refuses to close again after the changes I had to make in order to have my .DLL work properly with a parent form:

vb Code:

Dim strExcelPath As String
    Dim xlBook As Excel.Workbook
    Dim xlWbs As Excel.Workbooks
    Dim accConn = New OleDbConnection
#Region "properties"
    Public Property ExcelPath() As String
        Get
            Return strExcelPath
        End Get
        Set(ByVal value As String)
            strExcelPath = value
            xlBook = xlWbs.Open(strExcelPath)
        End Set
    End Property
    Public Property AccessConn() As OleDbConnection
        Get
            Return accConn
        End Get
        Set(ByVal value As OleDbConnection)
            accConn = value
        End Set
    End Property
#End Region
    Public Sub XLMain()
        ReadXL()
        ImportXL()
    End Sub
    'Functions/Subs
    Public Sub ReadXL()
        Dim xlApp = New Excel.Application
        xlWbs = xlApp.Workbooks
        Dim xlSheet = xlBook.Worksheets(1)
        Try
            ordernumber = xlSheet.Cells(5, "C").Value.ToString
            owner1 = xlSheet.Cells(6, "C").Value.ToString
            county = xlSheet.Cells(6, "I").Value.ToString
            fulladdress = xlSheet.Cells(7, "C").Value.ToString
            address = Mid(fulladdress, 1, InStr(fulladdress, ",") - 1)
            halfaddress = Mid(fulladdress, address.Length + 2)
            zip = Mid(fulladdress, fulladdress.Length - 4, 5)
            city = Mid(halfaddress, 1, InStr(halfaddress, ",") - 1)
            state = Mid(fulladdress, address.Length + city.Length + 4, 2)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        xlBook.Close()
        xlApp.Quit()
        xlApp = Nothing
        xlWbs = Nothing
        xlBook = Nothing
        xlSheet = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
    Public Sub ImportXL()
        Dim cmdInsert As New OleDbCommand("INSERT INTO Orders (OrderNumber, Owner1Full, County, City, State, Address, ZipCode) VALUES ('" & ordernumber & "', '" & owner1 & "', '" & county & "', '" & city & "', '" & state & "', '" & address & "', '" & zip & "')", accConn)
        accConn.Open()
        Try
            cmdInsert.ExecuteNonQuery()
        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
        accConn.Close()
    End Sub
End Class

It is back to closing EXCEL.EXE when the parent Form closes. Which is unacceptable as this will be a service running 24/7

vb Code:

Public Sub callstuff()
        tempExcel.AccessConn = accConn
        tempExcel.ExcelPath = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
        tempExcel.XLMain()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

Fixed.