[RESOLVED] Try\Catch makes Excel hang in Task Manager

[TysonLPrice]

I'm losing it over this one...In the code below if the Tray/Catch is uncommented the Excel application will stay in the task manager. It releases Excel just fine without the try/Catch. As soon as I put it back in it hangs up. I'm really stumped.

VS2013 - Windows 10
Reference is to MicroSoft Excel Object Library 14.0

Imports Microsoft.Office.Interop



Public Class Form1
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet

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

        ' Try

        Dim xl As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        xl = New Excel.Application
        xl.DisplayAlerts = False
        xlWorkBooks = xl.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\manualshort.xlsx")
        xlWorksheet = xl.Sheets(1)
        xlWorksheet.Activate()

        xl.Visible = False

        xlWorkBook.Close()
        xlWorksheet = Nothing

        xl.Quit()

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()


        'Catch ex As Exception
        '    MessageBox.Show("Caught")
        'End Try



    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
            Exit Sub
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Class

[Shaggy Hiker]

Are you saying that just having it is a problem? In other words, it's not catching anything, it's just the presence that is causing an issue?

If so, put a messagebox in the Catch block of your ReleaseComObject exception handler. After all, if that is throwing an exception, it would be quietly swallowed...and most likely the object would not be released.

[TysonLPrice]

Are you saying that just having it is a problem? In other words, it's not catching anything, it's just the presence that is causing an issue?

If so, put a messagebox in the Catch block of your ReleaseComObject exception handler. After all, if that is throwing an exception, it would be quietly swallowed...and most likely the object would not be released.

Good point. I added MessageBox.Show(ex.Message) to it and it is not producing an error. What I'm saying is the presence of the try catch makes it hang. I can reproduce it over and over again.

[TysonLPrice]

And I don't know if this is relevant or not but when I stop the run Excel closes. I would have expected it to stay in the task manager even though the program stopped.

[Shaggy Hiker]

Yeah, that sounds relevant. It sounds like something is holding the Excel alive while the program is living. Like it hasn't gotten around to cleaning it up, despite your attempt with GC.Collect.

No error message, no exceptions, and the presence of a try...Catch is enough to keep some variable around. Sounds internal, though not infernal, to me.

[ChrisE]

I end Excel this way

'...code

        'Save Excel
        oSheet.SaveAs("E:\Test.xlsx")
        Me.Cursor = Cursors.Default
        oXL.Quit()
        releaseObject(oXL)
        releaseObject(oWB)
        End_Excel_App(datestart, dateend)
    End Sub

    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()
        End Try
    End Sub



    Dim datestart As Date = Date.Now
    Dim dateend As Date = Date.Now

    Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
        Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
        For Each Process As Process In xlp
            If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                Process.Kill()
                Exit For
            End If
        Next
    End Sub

hth

[techgnome]

Personally I'd move this:

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()

into a Finally block... that way it'll run exception or not.

-tg

[TysonLPrice]

Personally I'd move this:

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()

into a Finally block... that way it'll run exception or not.

-tg

Thanks for responding...I had already tried that with "no joy".

[TysonLPrice]

I end Excel this way

'...code

        'Save Excel
        oSheet.SaveAs("E:\Test.xlsx")
        Me.Cursor = Cursors.Default
        oXL.Quit()
        releaseObject(oXL)
        releaseObject(oWB)
        End_Excel_App(datestart, dateend)
    End Sub

    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()
        End Try
    End Sub



    Dim datestart As Date = Date.Now
    Dim dateend As Date = Date.Now

    Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
        Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
        For Each Process As Process In xlp
            If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                Process.Kill()
                Exit For
            End If
        Next
    End Sub

hth

Thanks for responding...no dice. You may want to look at post #2. I changed my per Shaggy Hikers suggestion.

[TysonLPrice]

Just for grins I tried it on Windows 7 and it has the same issue. A clunky work-around is to call the Excel routine from a procedure with a Try/Catch and in the called procedure do not have a Try/Catch. The errors will "bubble up". I tried it in the example below. I call the procedure without Try/Catch and put a divide by zero at the end of it to create an error. Excel closed and the divide by zero bubbled up. I really hate a clunky work-around like that but I'm also on the clock. I'm going to leave this unresolved. Maybe jmcilhinney will look at it. I'm curious about what his take on it will be. This is the work-around:

Imports Microsoft.Office.Interop

Public Class Form1
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet

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

        Try

            TestExcel()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub TestExcel()

        Dim xl As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        xl = New Excel.Application
        xl.DisplayAlerts = False
        xlWorkBooks = xl.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\manualshort.xlsx")
        xlWorksheet = xl.Sheets(1)
        xlWorksheet.Activate()

        xl.Visible = False

        xlWorkBook.Close()
        xlWorksheet = Nothing

        xl.Quit()

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()

        Dim x As Integer = 0
        Dim y As Integer = 0
        Dim z As Integer = 0
        x = y / z

    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
            Exit Sub
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Class

[ChrisE]

well I tried this and Excel closed

Imports Microsoft.Office.Interop

Public Class Form4
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            Dim xl As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing

            xl = New Excel.Application
            xl.DisplayAlerts = False
            xlWorkBooks = xl.Workbooks
            xlWorkBook = xlWorkBooks.Open("E:\Book.xlsx")
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()

            xl.Visible = False

            xlWorkBook.Close()
            xlWorksheet = Nothing

            xl.Quit()

            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xl)

            'GC.Collect()


        Catch ex As Exception
            MessageBox.Show("Caught")
        End Try

    End Sub

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

    End Sub
End Class

have you had any Office Updates recently ?

[TysonLPrice]

well I tried this and Excel closed

Imports Microsoft.Office.Interop

Public Class Form4
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            Dim xl As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing

            xl = New Excel.Application
            xl.DisplayAlerts = False
            xlWorkBooks = xl.Workbooks
            xlWorkBook = xlWorkBooks.Open("E:\Book.xlsx")
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()

            xl.Visible = False

            xlWorkBook.Close()
            xlWorksheet = Nothing

            xl.Quit()

            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xl)

            'GC.Collect()


        Catch ex As Exception
            MessageBox.Show("Caught")
        End Try

    End Sub

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

    End Sub
End Class

have you had any Office Updates recently ?

I just tried exactly what you says works and it doesn't on my machine. I'm not sure what updates I have because this is a work computer, and I don'r know where to look. It also doesn't work on Windows 7. But hearing that it works on someone else's machine kind of makes me feel better but not. It deepens the mystery.

[ChrisE]

well I use Win 7 and it end's Excel, perhaps somebody else can try the code in post#11 with Win10

if it works there also then it must be your Work computer

EDIT:
which Excel version is on the computer ?

[TysonLPrice]

well I use Win 7 and it end's Excel, perhaps somebody else can try the code in post#11 with Win10

if it works there also then it must be your Work computer

That would be nice and I appreciate you trying it "real time". By the way...are you on Microsoft Excel Object Library 14.0? Someone hear at work is trying to help and he gets the error on his PC. Maybe it is our environment.

[ChrisE]

I use Win 7 wilth all the updates and, yes I use Microsoft Excel Object Library 14.0
I'm also quit intrested in this problem, I do have a lot of Excel Stuff for users

[kareninstructor]

I've seen quickness with private variable for Excel automation. Have not done anything in ages with Excel automation, dumped it years ago for Open XML for Excel.

Anyways it would be interesting to see what happens with the following code I dug up. Current version of Microsoft.Office.Interop.Excel is 15.0.0, I'd expect the same results on Windows 7 and 10.

Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Operations
    ''' <summary>
    ''' Open file, see what the current active sheet is
    ''' then change it.
    ''' </summary>
    ''' <param name="pFileName">File to open</param>
    ''' <param name="pSheetName">Make active sheet</param>
    Public Sub SetDefaultSheet(pFileName As String, pSheetName As String)

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

        xlApp = New Excel.Application With {
            .DisplayAlerts = False
        }
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(pFileName)

        xlApp.Visible = False
        xlWorkSheets = xlWorkBook.Sheets

        '
        ' Here I added how to get the current active worksheet
        '
        Dim activeSheetInWorkBook =
                CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

        Console.WriteLine(activeSheetInWorkBook.Name)

        Marshal.FinalReleaseComObject(activeSheetInWorkBook)
        activeSheetInWorkBook = Nothing

        For index As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
            If xlWorkSheet.Name = pSheetName Then

                xlWorkSheet.Activate()

                xlWorkSheet.SaveAs(pFileName)
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

                Exit For

            End If

            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing

        Next

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

        ReleaseExcelObject(xlWorkSheets)
        ReleaseExcelObject(xlWorkSheet)
        ReleaseExcelObject(xlWorkBook)
        ReleaseExcelObject(xlWorkBooks)
        ReleaseExcelObject(xlApp)

    End Sub
    Protected Sub ReleaseExcelObject(excelObject As Object)
        Try
            If excelObject IsNot Nothing Then
                Marshal.ReleaseComObject(excelObject)
                excelObject = Nothing
            End If
        Catch ex As Exception
            excelObject = Nothing
        End Try
    End Sub
End Class

Called

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

    Dim ops As New Operations
    ops.SetDefaultSheet(Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"), "People")
End Sub

Edit this releases too

Protected Sub ReleaseExcelObject(excelObject As Object)
    If excelObject IsNot Nothing Then
        Marshal.ReleaseComObject(excelObject)
        excelObject = Nothing
    End If

    'Try
    'Catch ex As Exception
    '    excelObject = Nothing
    'End Try
End Sub

[ident]

I'm losing it over this one...In the code below if the Tray/Catch is uncommented the Excel application will stay in the task manager. It releases Excel just fine without the try/Catch. As soon as I put it back in it hangs up. I'm really stumped.

VS2013 - Windows 10
Reference is to MicroSoft Excel Object Library 14.0

Imports Microsoft.Office.Interop



Public Class Form1
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet

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

        ' Try

        Dim xl As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        xl = New Excel.Application
        xl.DisplayAlerts = False
        xlWorkBooks = xl.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\manualshort.xlsx")
        xlWorksheet = xl.Sheets(1)
        xlWorksheet.Activate()

        xl.Visible = False

        xlWorkBook.Close()
        xlWorksheet = Nothing

        xl.Quit()

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()


        'Catch ex As Exception
        '    MessageBox.Show("Caught")
        'End Try



    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
            Exit Sub
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Class

Excel does not quit because your application is still holding references to COM objects.

[TysonLPrice]

Excel does not quit because your application is still holding references to COM objects.

My post is about why...I can't see what I'm doing wrong

[ChrisE]

hi Tyson

go back to your code in Post#1

and change this line

 xl.Visible = False

to

 xl.Visible = True

is there any Message displayed ?

[TysonLPrice]

No messages...the spreadsheet just becomes visible.

[ChrisE]

I'm losing it over this one...In the code below if the Tray/Catch is uncommented the Excel application will stay in the task manager. It releases Excel just fine without the try/Catch. As soon as I put it back in it hangs up. I'm really stumped.

VS2013 - Windows 10
Reference is to MicroSoft Excel Object Library 14.0

Imports Microsoft.Office.Interop



Public Class Form1
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet

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

        ' Try

        Dim xl As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        xl = New Excel.Application
        xl.DisplayAlerts = False
        xlWorkBooks = xl.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\manualshort.xlsx")
        xlWorksheet = xl.Sheets(1)
        xlWorksheet.Activate()

        xl.Visible = False

        xlWorkBook.Close()
        xlWorksheet = Nothing

        xl.Quit()

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()
        GC.WaitForFullGCComplete() ' <-- Add this

        'Catch ex As Exception
        '    MessageBox.Show("Caught")
        'End Try



    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
            Exit Sub
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Class

add the line I marked in Blue

[ChrisE]

No messages...the spreadsheet just becomes visible.

did you click on close in Excel ? did any Message appear there ?

[ChrisE]

another option is to use the ProcessID, open the Taskmanager and see the Excel Instance
when it is created and then closed after operation(s) read and write

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel


Public Class Form5

    Private Declare Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hWnd As Integer, ByRef lpdwProcessId As IntPtr) As IntPtr


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'read Value
        Dim Path As String = "E:\Book1.xlsx"
        Dim myExcel As ApplicationClass = New ApplicationClass
        Dim myWorkBook As Workbook = myExcel.Workbooks.Open(Path)
        Dim myWorkSheets As Sheets = myWorkBook.Sheets
        Dim myWorkSheet As Worksheet = CType(myWorkSheets(1), Excel.Worksheet)
        Dim cell As String = CStr(myWorkSheet.Range("B9").Value)
        TextBox1.Text = cell 'show Value from Excel sheet
        killExcelInstanceById(myExcel) 'Exit Excel with ID
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'write Value
        Dim Path As String = "E:\Book1.xlsx"
        Dim myExcel As ApplicationClass = New ApplicationClass
        Dim myWorkBook As Workbook = myExcel.Workbooks.Open(Path)
        Dim myWorkSheets As Sheets = myWorkBook.Sheets
        Dim myWorkSheet As Worksheet = CType(CType(myWorkSheets(1), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
        With myWorkSheet
            'do Stuff
            .Range("B9").Value = "Success"
            'etc...
        End With
        myWorkBook.Save()
        killExcelInstanceById(myExcel)
    End Sub

    Private Shared Sub killExcelInstanceById(ByRef myExcelInstance As Excel.Application)
        Dim processId As IntPtr
        ''API Funktion, out val: processId
        GetWindowThreadProcessId(myExcelInstance.Hwnd, processId)
        'create Process
        Dim excelProcess As Process = Process.GetProcessById(processId.ToInt32())
        MsgBox(processId) 'show the ID from the Process
        excelProcess.Kill()
    End Sub

   
End Class

this will make sure Excel Instances from other users are not closed

HTH

[TysonLPrice]

I'm marking this resolved and thanks to everyone that responded. As sloppy and personally embarrassing as it is I'm going to allow Excel to hang in this case. It is a quarterly process that runs the one time on one user's machine. It was the principle to me. Maybe down the road I can come back to it or the next time I set something like this up again.

[TysonLPrice]

Is has been about a year since I posted this and it happened again in a new program I just wrote. I googled it and was happy to get such an exact hit on the subject line until I saw it was my old post and I never figured it out. I'm just going to leave the try/catch out this time and move. I can reproduce it over and over again. I just updated the thread in case a fresh set of eyes wants to chime in. At this point I'm only interested in why, not work arounds.

[squatman]

I was really hoping to figure this out I have hit and miss experiences for users when writing to Excel, so really wanted to nail it down.

I've just spent a good few hours trying all the common solutions in various orders, I couldn't even get the "Microsoft Excel" entry in Task Manager Background Processes to disappear once I closed my application, never mind when disposed of in my app.

After trying different things for hours it started at least now disappearing from Background Processes after I close my app, but this is with minimal code and the only thing different to when I started is:

Dim xlApp As Excel.Application
        xlApp = New Excel.Application
 
        Dim xlWb As Excel.Workbook
        xlWb = xlApp.Workbooks.Open(copyTo)
 
        Dim xlWs As Excel.Worksheet
 
        xlWs = xlWb.Worksheets(1)
        xlWs.Activate()

instead of:

Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook = xlApp.Workbooks.Open(copyTo)
        Dim xlWs As Excel.Worksheet
 
        xlWs = xlWb.Worksheets(1)
        xlWs.Activate()

Which you're already doing.

In your scenario does the Excel instance in Background Processes disappear once your application is closed?

[TysonLPrice]

I closed the book on this one years ago. Thanks for responding though.