asked on

I need help closing an Excel object in my ASP.Net application

Hi Experts,
In my ASP.Net 3.5 application I open and close an Excel object like in my code below:

'Open Excel object
objXL = New Microsoft.Office.Interop.Excel.Application
objWkB = objXL.Workbooks.Open(strLastQtrPSBD12)
objXL.Visible = False

'close Excel object
objWkB.Close(False)
objXL.Quit()

objRange = Nothing
objWkB = Nothing
objXL = Nothing

I want to be make sure that I close my Excel object when there is an Exception error. How do I check
If my Excel object is opened, so that I may close it?

Thanks in advance,
mrotor

dj_alik

''Releases COM objectsImports System.Runtime.InteropServices     ''Releases COM objects    Private Sub ReleaseComponent(ByVal component As Object)        Dim count As Integer = 0         Do            count = Marshal.ReleaseComObject(component)        Loop While count <> 0    End Sub''Releases COM objects
Imports System.Runtime.InteropServices

    ''Releases COM objects
    Private Sub <strong class="highlight">ReleaseComponent</strong>(ByVal component As Object)
        Dim count As Integer = 0

        Do
            count = Marshal.ReleaseComObject(component)
        Loop While count <> 0
    End Sub

Open in new window

Public Sub CloseExcel(ByVal app As Excel.Application, _        ByVal interact As Boolean)         Dim wbk As Excel.Workbook        Dim wrk As Excel.Worksheet         app.Interactive = Interact        app.DisplayAlerts = Interact         If Not app.Workbooks Is Nothing Then            For Each wbk In app.Workbooks                For Each wrk In wbk.Worksheets                    wrk = Nothing                Next                wbk.Close(False)                wbk = Nothing            Next        End If        app.Quit()    End Sub    Public Sub CloseExcel(ByVal app As Excel.Application, _
        ByVal interact As Boolean)

        Dim wbk As Excel.Workbook
        Dim wrk As Excel.Worksheet

        app.Interactive = Interact
        app.DisplayAlerts = Interact

        If Not app.Workbooks Is Nothing Then
            For Each wbk In app.Workbooks
                For Each wrk In wbk.Worksheets
                    wrk = Nothing
                Next
                wbk.Close(False)
                wbk = Nothing
            Next
        End If
        app.Quit()
    End Sub

Open in new window

dj_alik

''Releases COM objects
Imports System.Runtime.InteropServices

    ''Releases COM objects
    Private Sub <strong class="highlight">ReleaseComponent</strong>(ByVal component As Object)
        Dim count As Integer = 0

        Do
            count = Marshal.ReleaseComObject(component)
        Loop While count <> 0
    End Sub

Open in new window

dj_alik

Sorry:

''Releases COM objects
Imports System.Runtime.InteropServices

    ''Releases COM objects
    Private Sub ReleaseComponent(ByVal component As Object)
        Dim count As Integer = 0

        Do
            count = Marshal.ReleaseComObject(component)
        Loop While count <> 0
    End Sub

Open in new window

dj_alik

Example:
xlapp.Quit()
'ss xlApp.UserControl = True
'ss xlApp.Visible = True

'Attempt to close excel - notorious problem with .net not closing
all instances of Excel in task manager!!
System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlSheet)
System.Runtime.InteropServices.Marshal.ReleaseComO bject(wb)
System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlapp)
xlapp = Nothing
wb = Nothing
xlSheet = Nothing

Carlos Villegas

Hi, if you want to effectively close the excel.exe instance, you need to call:

GC.Collect()

Open in new window

after call:

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(...)

Open in new window

Example:

objXL = New Microsoft.Office.Interop.Excel.Application
objWkB = objXL.Workbooks.Open(strLastQtrPSBD12)
objXL.Visible = False

'close Excel object
objWkB.Close(False)
objXL.Quit()

'Mark instances to finallize
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWkB)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objXL)

objRange = Nothing
objWkB = Nothing
objXL = Nothing

'This will close your Excel instance
GC.Collect()

Open in new window

Carlos Villegas

This is a better example of how to handle your Excel instance inside a try/catch block to effectively close it if an exception is raised:

Dim strLastQtrPSBD12 As String = "SheetName"
Dim objXL As Microsoft.Office.Interop.Excel.Application = Nothing
Dim objWkB As Microsoft.Office.Interop.Excel.Workbook = Nothing

Try
    objXL = New Microsoft.Office.Interop.Excel.Application
    objWkB = objXL.Workbooks.Open(strLastQtrPSBD12)
    objXL.Visible = False

    'close Excel object
    objWkB.Close(False)

    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWkB)
Catch ex As Exception
    'Log error???
Finally
    objXL.Quit()
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objXL)

    'This will close your Excel.exe process
    GC.Collect()
End Try

Open in new window

dj_alik

very good example by yv989c

Carlos Villegas

@dj_alik thanks buddy, not long ago I passed through this.

ASKER CERTIFIED SOLUTION

Rick

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dj_alik

rick_gwu's solution good for server side

mainrotor

ASKER

rick_gwu,
I followed your example, but first I check for the process id created when my Excel object was opened. Then I make sure to only close the correct process.

Rick

Good!

Glad to help.