.net 4.5 Excel.Application - how to cancel elegantly

Question

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

Are you calling `.Quit` on Excel's Application object anywhere? See https://stackoverflow.com/questions/17777545/closing-excel-application-process-in-c-sharp-after-data-access — Bradley Uffner, Jan 08 '16 at 18:47
I tried various version of activeworkbook.close(0) and activeworkbook.close or thisworkbook.close and adding oExcel.quit.. none seem to work — BobNoobGuy, Jan 08 '16 at 18:58

Blindy · Answer 1 · 2016-01-08T19:25:52.113

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.

Another reason to avoid COM when it comes to Office products, is if you try to run them in a web environment, your code will crash and burn even faster. Office is just not meant to be run on a server. Tools like EPPluss are the way to go (I would imagine NPOI as well). — krillgar, Jan 08 '16 at 19:27
Wow thanks for the info. This is the only way I know how to generate data where user can have the flexibility. I will read about NPOI and learn on how to apply it for my solution Thank you! — BobNoobGuy, Jan 08 '16 at 19:37

score 0 · Accepted Answer · edited May 23 '17 at 11:59

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

.net 4.5 Excel.Application - how to cancel elegantly

2 Answers2