Closing out of EXCEL completly after VB.net 2008 processing

Dear Experts,

I have a VB.net 2008  application that reads & processes an Excel speadsheet (2007), everything works fine except it leaves an  Excel process  (in windows task manager) running. If I then try to open the excel file is locked.



 Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim WkSht As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Open(filePath)
        WkSht = wBook.ActiveSheet()

'code to read and process data works fine
'.......... then comes the clean up
' the code below does not close the EXCEL processe
            wBook.Close()
            excel.Quit()
            releaseObject(excel)
            releaseObject(wBook)
            releaseObject(WkSht)
        Catch ex As Exception
            'Close Excel WRBook and Excel
            MessageBox.Show(ex.Message & " Error in SQL update  " & ex.InnerException.Source)
            wBook.Close()
            excel.Quit()
            releaseObject(excel)
            releaseObject(wBook)
            releaseObject(WkSht)
        End Try


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

Any ideas suggestions?

Thank You
Cin
cin_champAsked:
Who is Participating?
 
the_oCommented:
Hi there. This is probably one of the most irratating problems I have come across. So here, for your convienence is a code snippet I always keep handy when doing XL "stuff"
#Region "Make sure Excel is Killed"
        Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr) As Integer
        Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
               (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
        Declare Function GetWindowThreadProcessId Lib "user32.dll" _
               (ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer
        Declare Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Integer) As IntPtr

        Public Shared Sub EnsureProcessKilled(ByVal MainWindowHandle As IntPtr, ByVal Caption As String)
            SetLastError(0)
            ' for Excel versions <10, this won't be set yet
            If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
          MainWindowHandle = FindWindow(Nothing, Caption)
            If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
          Exit Sub ' at this point, presume the window has been closed.
            Dim iRes, iProcID As Integer
            iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
            If iProcID = 0 Then ' can’t get Process ID
                If EndTask(MainWindowHandle) <> 0 Then Exit Sub ' success
                Throw New ApplicationException("Failed to close.")
            End If
            Dim proc As System.Diagnostics.Process
            proc = System.Diagnostics.Process.GetProcessById(iProcID)
            proc.CloseMainWindow()
            proc.Refresh()
            If proc.HasExited Then Exit Sub
            proc.Kill()
        End Sub

        Public Shared Function PleaseJustCloseExcel(ByRef xlApp As Excel.Application) As Boolean
            Dim result As Boolean = True
            Try
                If Not xlApp.Workbooks Is Nothing Then
                    For Each wb In xlApp.Workbooks
                        For Each ws In wb.Worksheets
                            Runtime.InteropServices.Marshal.ReleaseComObject(ws)
                            ws = Nothing
                        Next
                        wb.Close(False)
                        Runtime.InteropServices.Marshal.ReleaseComObject(wb)
                        wb = Nothing
                    Next
                    xlApp.Workbooks.Close()
                End If
                xlApp.DisplayAlerts = False
                xlApp.Quit()

                GC.Collect()
                GC.WaitForPendingFinalizers()

                Dim sVer As String = xlApp.Version
                Dim iHandle As IntPtr = IntPtr.Zero
                If Val(sVer) >= 10 Then iHandle = New IntPtr(CType(xlApp.Parent.Hwnd, Integer))

                EnsureProcessKilled(iHandle, xlApp.Caption)
            Catch comEX As Runtime.InteropServices.COMException
                result = True
            Catch ex As Exception
                result = False
            End Try
            Return result
        End Function
#End Region

Open in new window

0
 
handyjayCommented:
Try releasing / closing the workbook first and then shutdown excel.

You may have a reference left open if you dispose excel first, leaving a reference to the workbook.
0
 
the_oCommented:
O and by the way. the usage in the form or class.
Dim ReportFile As Excel.Application = Nothing
            Dim WorkBook As Excel._Workbook = Nothing

            Try
                'Start Excel
                ReportFile = New Excel.Application()
                'uniquely mark this instance of Excel
                ReportFile.Caption = System.Guid.NewGuid.ToString.ToUpper

                ReportFile.Visible = False

                'Open Workbook
                WorkBook = ReportFile.Workbooks.Open(dlgSelectXLFile.FileName)
                '...
                '...
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                Do While Not PleaseJustCloseExcel(ReportFile)
                    WorkBook.Close()
                    ReportFile.Quit()
                    releaseObject(ReportFile)
                    releaseObject(WorkBook)
                Loop
            End Try

Open in new window

0
 
Avinash DesaiSr Software EngineerCommented:
Try closing workbook, sheet and then save the excel file and then try to call releaseObject. Then it works fine.
0
 
cin_champAuthor Commented:
Awesome! Thank you so much!
Clean & accurate!

You saved the day!  :)
Cin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.