Excel still running after close vb.net app

Question

I'm having some problems closing Excel files. I am doing a program that opens some Excel files, uses the information and then close them.
I tried some different codes, but they didn't work because the EXCEL process is still running.

My first code:

Dim aplicacaoexcel As New Excel.Application
Dim livroexcel As Object
Dim folhaexcel As Excel.Worksheet

livroexcel = aplicacaoexcel.Workbooks.Open("C:\Users\LPO1BRG\Desktop\Software Fiabilidade\Tecnicos.xlsx", UpdateLinks:=False, ReadOnly:=False, Password:="qmm7", WriteResPassword:="qmm7")
folhaexcel = livroexcel.sheets("Folha1")

aplicacaoexcel.DisplayAlerts = False
aplicacaoexcel.Visible = False
folhaexcel = Nothing
livroexcel.Close()
livroexcel = Nothing
aplicacaoexcel.Quit()
aplicacaoexcel = Nothing

Then I added this: System.GC.Collect() but it still not closing the Excel process.

Now I am trying this:

Dim process() As Process = system.Diagnostics.Process.GetProcessesByName("EXCEL")

For Each p As Process In process
   p.Kill()
Next

Actually, this one is working, but it closes all the Excel files (even those that are not opened by my program).

What can I do to close just the Excel files opened by my program? :)

Answer 1

Releasing the Excel.Application Interop COM object is a little bit trickier than other Office Interop objects, because some objects are created without your knowledge, and they all must be released before the main Application can be actually closed.

These objects include:

• The Excel.Application
  
• The Excel.Application.WorkBooks collection
  
• The WorkBooks collection opened WorkBook
  
• The WorkBook Sheets collection
  
• The Sheets collection referenced Worksheet
  

These objects must all be released in order to terminate the EXCEL process.

A simple solution is to use explicit declarations/assignment for all the COM objects:

Dim ExcelApplication As New Microsoft.Office.Interop.Excel.Application()
Dim ExcelWorkbooks As Workbooks = ExcelApplication.Workbooks
Dim MyWorkbook As Workbook = ExcelWorkbooks.Open("[WorkBookPath]", False)
Dim worksheets As Sheets = MyWorkbook.Worksheets
Dim MyWorksheet As Worksheet = CType(worksheets("Sheet1"), Worksheet)

When you're done, release them all:

Imports System.Runtime.InteropServices

Marshal.ReleaseComObject(MyWorksheet)
Marshal.ReleaseComObject(worksheets)

MyWorkbook.Close(False)   '<= False if you don't want to save it!
Marshal.ReleaseComObject(MyWorkbook)

ExcelWorkbooks.Close()
Marshal.ReleaseComObject(ExcelWorkbooks)

ExcelApplication.Quit()
Marshal.FinalReleaseComObject(ExcelApplication)

Marshal.CleanupUnusedObjectsInCurrentContext()

Answer 2

I'm a little rusty on my VB and don't have access to Visual Studio right now to test this, but I will try writing the code from memory.

The problem you are running into is that setting an object equal to Nothing still leaves the object allocated in memory and doesn't dispose of the object entirely. Even the Close() and Quit() methods of the object still leave it allocated in memory in case the program needs to access them again later. The Kill() method works because it simply kills the Excel application that is running in memory, which closes any open Excel documents as well as the ones used by COM objects. What you want to do is dispose of the specific COM objects that your application is using.

Try this change in your code though.

Dim aplicacaoexcel As New Excel.Application
Dim livroexcel As Object
Dim folhaexcel As Excel.Worksheet

livroexcel = aplicacaoexcel.Workbooks.Open("C:\Users\LPO1BRG\Desktop\Software Fiabilidade\Tecnicos.xlsx", UpdateLinks:=False, ReadOnly:=False, Password:="qmm7", WriteResPassword:="qmm7")
folhaexcel = livroexcel.sheets("Folha1")

aplicacaoexcel.DisplayAlerts = False
aplicacaoexcel.Visible = False

DisposeComObj(folhaexcel)
DisposeComObj(livroexcel)
DisposeComObj(aplicacaoexcel)

Then add the following to your application as well.

Private Sub DisposeComObj(ByRef Reference As Object)
    Try        
        Do Until _
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)<=0
        Loop
    Catch
    Finally
        Reference = Nothing
    End Try
End Sub

Hopefully my memory is serving me well. I haven't written any code in VB or C# in more than a year and feel very out of practice. I only saw your question because I clicked on the wrong link and remembered how I used to struggle with garbage collection when I started programming.

Answer 3

Try something like workbook.Save, workbook.close

Answer 4

At the start of the process put this:

dim ExcellProcesses as string =""    
Dim startprocesses() As Process = system.Diagnostics.Process.GetProcessesByName("EXCEL")    

For Each sP As Process In startprocesses    
   ExcellProcesses = ExcellProcesses & ";" & sP.id() & ";"
Next

At the end of the process put:

Dim process() As EndProcesses = system.Diagnostics.Process.GetProcessesByName("EXCEL")

For Each eP As Process In Endprocesses
   if instr(ExcellProcesses,  ";" & eP.id() & ";")=0 then eP.kill
Next

this way only the newly opened Excel process will be killed.