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


mainrotorAsked:
Who is Participating?
 
RickConnect With a Mentor Commented:
Dim pr() As Process = Process.GetProcessesByName("excel")

For Each p As Process In pr
       p.CloseMainWindow()
Next
0
 
dj_alikCommented:
''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

0
 
dj_alikCommented:
''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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
dj_alikCommented:
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

0
 
dj_alikCommented:
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
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
 
dj_alikCommented:
very good example by yv989c
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@dj_alik thanks buddy, not long ago I passed through this.
0
 
dj_alikCommented:
rick_gwu's solution  good for  server side
0
 
mainrotorAuthor Commented:
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.
0
 
RickCommented:
Good!

Glad to help.
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.