CODEWITHSUNDEEP
.netvb.netexcel
BobNoobGuy
BobNoobGuy

Reputation: 1645

.net 4.5 Excel.Application - how to cancel elegantly

here is my current code: I have a function that builds an excel file. The file only has one workbook and one worksheet. this process takes about 10 minutes.

I would like to allow user to cancel this process, without saving the file. but if I cancel the program excel.exe lingers in task manager. How do I quit the program elegantly?

here is my simplified code: 

Dim oExcel As Excel.Application = New Excel.Application
oExcel.Workbooks.Add()
Dim sht As New Excel.Worksheet
sht = oExcel.Worksheets.Add

if user cancel the function here is my code: to try to exit elegantly

If BackgroundWorker1.CancellationPending Then
  e.Cancel = True
  releaseObject(sht)
  releaseObject(oExcel.ActiveWorkbook)
  releaseObject(oExcel)
  oExcel = Nothing
  GC.Collect()
  Exit Sub
End If

this is the releaseObject function

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

Upvotes: 1

Views: 136

Answers (2)

Blindy
Blindy

Reputation: 67487

There's nothing elegant about working with Office interop, which is why you should avoid it entirely. There's plenty of libraries out there that support DOM-level access to Excel spreadsheets (like NPOI).

I see you sort of read about the difficulties in releasing COM objects, but you missed the kicker here: the compiler is holding hidden references to temporary objects you can never free. Plus you free the wrong things. Plus setting parameters to Nothing does, ironically enough, nothing.

Since you don't post all your code I can't point out all the instances where you mess this up, but the very first instance is this:

oExcel.Workbooks.Add()

Here the compiler will hold a reference to oExcel.Workbooks that you never free. A good rule of thumb with COM in .NET is to never use two dots, and release everything in reverse order of creation.

Also another issue with your code is this line: 

releaseObject(oExcel.ActiveWorkbook)

This is what sort of tipped me off that you read about the issues here, but kind of missed the point. Calling that property actually returns another instance you need to track and release, it won't release the previous active workbook object.

Upvotes: 2

BobNoobGuy
BobNoobGuy

Reputation: 1645

I end up folowing instruction here: Excel process still runs after closing in VB.net

Basically comparing processID of all excel before I create the object and after I create the object it's dangerous because if user open excel during this process I may kill the wrong one... but all other option does not seem to work for me :(

    Private mAllExcelBefore() As Process
    Private Sub ExcelProcessInit()
        Try
            mAllExcelBefore = Process.GetProcessesByName("Excel")
        Catch ex As Exception
        End Try
    End Sub
    Private Sub CloseExcel()
        Dim mAllExcelAfter() As Process
        Dim bFound As Boolean
        Try
            mAllExcelAfter = Process.GetProcessesByName("Excel")
            If mAllExcelAfter.Length > 0 Then
                For i As Integer = 0 To mAllExcelAfter.Length - 1
                    bFound = False
                    For j As Integer = 0 To mAllExcelBefore.Length - 1
                        If mAllExcelAfter(i).Id = mAllExcelBefore(j).Id Then
                            bFound = True
                            Exit For
                        End If
                    Next

                    If Not bFound Then
                        mAllExcelAfter(i).Kill()
                    End If
                Next
            End If
        Catch ex As Exception
        End Try
    End Sub

Upvotes: 0

Related Questions