Excel Process not closing in VB.net

Question

I am creating an excel file using interop.excel and the process is not closing. This is the code i am trying to use.

 Private Sub converToExcel(fileLoc As String, ds As DataSet)
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkBooks As Excel.Workbooks
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Excel.Application
    xlWorkBooks = xlApp.Workbooks
    xlWorkBook = xlWorkBooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            xlWorkSheet.Columns.NumberFormat = "@"
            xlWorkSheet.Cells(i + 1, j + 1) = String.Format("{0}", ds.Tables(0).Rows(i).Item(j).ToString())
        Next
    Next

    xlWorkSheet.SaveAs(fileLoc)
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkBooks)
    releaseObject(xlApp)

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

I think i am missing a COM object but cant seem to find a solution. Also as a note, this is running on 64-bit Windows 8. Any help would be great! Thanks

Solution 1

Manual memory management like this just never works. This is a problem that's been known for very a long time and the core reason that garbage collectors were invented. Programmers just forever forget to release memory.

It gets extra hard when you can't see the memory being used. Which is certainly the case in your code, the xlWorkSheet.Cells(i + 1, j + 1) expression uses no less than three references. One for the range object returned by the Cells property, one for a sub-range object selected by i+1 and another for the sub-range object selected by j+1. Very nice syntax sugar provided by the VB.NET language, writing COM code without it is pretty doggone painful. But not helpful to let you see the references. Not only can't you see it in your source code, there is absolutely nothing the debugger can do to help you see them either.

This is very much a solved problem in .NET, it has a garbage collector and it can see everything. The most basic problem is that you don't give it a chance to solve your problem. The mistake you made is that you stopped. Probably by setting a breakpoint on the last statement and then looking in Task Manager and seeing Excel.exe still running. Yes, that's normal. Garbage collection is not instant.

Calling GC.Collect() is supposed to make it instant, but that doesn't work in the specific case of running the Debug build of your project. The lifetime of local variables gets then extended to the end of the method, help you see them in the Autos/Locals/Watch window. In other words, GC.Collect() doesn't actually collect any of the interface references. More about that behavior in this post.

The simple workaround is to not stop. Keep doing useful things to give the garbage collector a reason to run. Or letting your program terminate since it is done, Excel terminates when the finalizer thread runs for the last time. Which works because the local variables that had the references are not in scope anymore.

But everybody wants the instant fix anyway. You get it by deleting all the releaseObject() calls. And doing it like this instead:

converToExcel(path, dset)
GC.Collect()
GC.WaitForPendingFinalizers()

Or in other words, force a collection after the method has returned. The local variables are no longer in scope so they can't hold on to an Excel reference. It will now also work when you debug it, like it already did when you ran the Release build without a debugger.

Solution 2

'Get the PID from the wHnd and kill the process.
' open the spreadsheet
ImportFileName = OpenFileDialog1.FileName
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wBook = excel.Workbooks.Open(ImportFileName)
hWnd = excel.Hwnd
Dim id As Integer = GetWindowThreadProcessId(hWnd, ExcelPID)

---

Sub CloseExcelFile()
        Try
            ' first try this
            wBook.Saved = True
            wBook.Close()
            excel.Quit()

            ' then this.
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
            excel = Nothing

            ' This appears to be the only way to close excel!
            Dim oProcess As Process
            oProcess = Process.GetProcessById(ExcelPID)
            If oProcess IsNot Nothing Then
                oProcess.Kill()
            End If

        Catch ex As Exception
            excel = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Solution 3

Finally solved :)

Private Function useSomeExcel(ByVal Excelfilename As String) 
  Dim objExcel As Excel.Application
  Dim objWorkBook As Excel.Workbook
  Dim objWorkSheets As Excel.Worksheet

  Dim datestart As Date = Date.Now
  objExcel = CreateObject("Excel.Application") 'This opens...  
  objWorkBook = objExcel.Workbooks.Open(Excelfilename) ' ... excel process
  Dim dateEnd As Date = Date.Now
  End_Excel_App(datestart, dateEnd) ' This closes excel proces
End Function

use this method

  Private Sub End_Excel_App(datestart As Date, dateEnd As Date)
    Dim xlp() As Process = Process.GetProcessesByName("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

This method closes especific process opened.

Solution 4

The GC.Collect makes not much sense where you placed it, if anything you should call it after you return from converToExcel. Also you may need to wait for finalizers to run. Personally I think Hans' answer is the way to go, but I know from personal experience writing office addins in C# that sometimes its necessary to do manual reference counting, in particular when you need to be compatible with older office versions. (There are many documented problems, in particular when handling events from office, which can only be reliably solved by manual reference counting. Also some COM libraries don't like at all when released in the wrong order by GC, but thats not the case with office.)

So on to the actual problem in your code: there are three intermediate COM objects not released here:

• xlWorkBook.Sheets returns a collection of type Excel.Sheets
• xlWorkSheet.Columns returns a COM object of type Excel.Range
• xlWorkSheet.Cells also returns an Excel.Range object

Besides this, if Marshal.ReleaseComObject throws an exception you did something wrong in your manual reference counting, therefore I wouldn't wrap it in an exception handler. When doing manual reference counting you must release every COM object once for every time it crosses the COM->NET boundary, meaning the Excel.Range objects need to be released in every iteration of the loop.

Here's code which properly terminates Excel for me:

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Private Sub converToExcel(fileLoc As String, ds As DataSet)
    Dim xlApp As New Excel.Application
    Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks
    Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Add(System.Reflection.Missing.Value)
    Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Sheets
    ' accessing the sheet by index because name is localized and your code will fail in non-english office versions
    Dim xlWorkSheet As Excel.Worksheet = xlWorkSheets(1)

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            ' couldn't this be moved outside the loop?
            Dim xlColumns As Excel.Range = xlWorkSheet.Columns
            xlColumns.NumberFormat = "@"
            Marshal.ReleaseComObject(xlColumns)

            Dim xlCells As Excel.Range = xlWorkSheet.Cells
            xlCells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j).ToString()
            Marshal.ReleaseComObject(xlCells)
        Next
    Next

    xlWorkSheet.SaveAs(fileLoc)
    'xlWorkBook.Close() -- not really necessary
    xlApp.Quit()

    Marshal.ReleaseComObject(xlWorkSheet)
    Marshal.ReleaseComObject(xlWorkSheets)
    Marshal.ReleaseComObject(xlWorkBook)
    Marshal.ReleaseComObject(xlWorkBooks)
    Marshal.ReleaseComObject(xlApp)
End Sub

If you want to be extra careful you'd want to handle exceptions from the office API and call ReleaseComObject inside finally-clauses. It can be helpful to define a generic wrapper and write using-clauses instead of try-finally (make the wrapper a structure not a class so you don't allocate the wrappers on the heap).

Solution 5

Try System.Runtime.InteropServices.Marshal.FinalReleaseComObject, that should help... also you should call xlWorkBook.Close() and xlapp.quit, if I recall correctly. First call them and then set them to nothing.

View more solutions

Comments

[Joe Smith]

I do call xlWorkBook.Close() and xlApp.Quit() before calling releaseObject. Tried using finalRelease, same result.

[Christian Sauer]

I think you order is wrong with the quit: I think you have to set the seehts to nothing, then quit the workbook then set it to nothing, then quot the app and quit it. But at further Investigatio I have seen tht you are calling gc collect only once - call it twice. If not, the objects are marked for release, but are not released.

[Zarat]

While your solution is generally the way to go it doesn't always work. When you need to be compatible with older Office versions there are many documented cases where you need to do manual reference counting, in particular when writing addins and handling events. Also some COM libraries (from Microsoft, but luckily not Office) don't like their objects being released in the wrong order by GC.

[user1703401]

That is pretty widespread claptrap. Common when an arbitrary small change has big side effects, making it all look like black magic. These "many documented cases" of course need a citation. Here is mine: blogs.msdn.com/b/visualstudio/archive/2010/03/01/…

[Zarat]

Sorry, can't find them from quick google searches, but when I researched this topic a few years ago there were plenty user reports of office not closing with managed addins (most common cause was subscribing to events), or even some (very rare) crashes due to GC releasing references at times office couldn't deal with. Probably all fixed in modern versions of office (and .NET 4 helps with the event subscribing), so only relevant if you must be backward compatible. There were also a few Microsoft KB articles about it, but they are really hard to find.

[Zarat]

You are right (with your posted link) that ReleaseComObject will cause trouble once office starts implementing their interfaces in .NET, but the link is wrong in that “COM object that has been separated from its underlying RCW cannot be used” is a problem. If you get that then you did your reference counting wrong (didn't increase the RCW refcount when you split ownership of the reference to multiple owners). If you do manual reference counting then you must go all the way and do it correctly.

[Zarat]

And don't get me wrong, you are absolutely right that being GC compatible is the future. Should only bother with ReleaseComObject when forced to be compatible with old stuff like Office 2000-2003 and/or .NET 2 - and like I said in my answer I think this is not the case for this question, he wouldn't run into any of the problems requiring ReleaseComObject

[Zarat]

Killing processes is rarely the right solution, it doesn't allow the target to do proper cleanup. For the posted question there exist clean solutions, so you should never have to fall back to killing excel.

[Shivachandra]

Sometimes it doesn't clean the Excel process and while generating excel repeatedly,it creates more then 1 excel process which supposed to slow up the process as well as the system.This was the problem which i was using and this solution worked fine in my case :)

[Zarat]

It might have worked in your own case, but it is not helpful to suggest it as a general purpose solution without explaining the consequences it has. For example it would kill any Excel process, including ones which the user opened. Most people don't want to install a program just to find out it'll kill their Excel instances just because the programmer couldn't think of a clean way to interact with Excel.

[Shivachandra]

Thanks @Zarat for the valuable information,Actually we never thought of such scenario,so now we need to change these line again.Thanks

[Benjamin W.]

Welcome to Stack Overflow! While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run.

[john ktejik]

error Count is not a member of System.Array