Excel wont close - Still runs in task manager (vb.net)

I Have the following code below which does not seem to kill the excel objects after they have been created.
I have tried the GC as well as the  System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference).

Public Function NameOfExcelSheet(ByVal strFileToOpen as String) as String
        Dim arrSheet(0) as String
        Dim i as Integer = 0
        objExcel = CreateObject("EXCEL.APPLICATION")
        objWorkBook = objExcel.Workbooks.Open(strFileToOpen)
        For Each objSheet In objWorkBook.Worksheets
            Array.Resize(arrSheet,i + 1)
            arrSheet(i) = objSheet.Name
            i = i + 1
        return arrSheet(0) ' Only need the first element
        objExcel.quit ' Quit Excel
        'Release objects
        objWorkBook = Nothing
        objExcel = Nothing
     '   objWorkBook.Close
     '   objExcel.quit ' Quit Excel
     '   objExcel = Nothing
     '   objWorkBook = Nothing
     '   objSheet = Nothing
     '   GC.Collect()
     '   GC.WaitForPendingFinalizers()
     '   ReleaseComObject(objExcel)
     '   ReleaseComObject(objWorkBook)
     '   ReleaseComObject(objSheet)
    End Function

Open in new window

Who is Participating?
avarmaavarmaConnect With a Mentor Commented:
The only way to identify a unique excel process would be to assign its window a name (a caption) - and then retrieve the handle to that window. The handle is one step away from the processId - which can then be used to kill the specific process.

You will need to set the caption for the application as follows
' set your application's window caption
App.Caption = System.Guid.NewGuid.ToString.ToUpper

Then you can call the EnsureProcessKilled function passing in the arguments (nothing, App.Caption) - e.g. EnsureProcessKilled(nothing, App.Caption)

Try that and see if it works.
' set your application's window caption
App.Caption = System.Guid.NewGuid.ToString.ToUpper

Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr) As Integer
    Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
           (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
    Declare Function GetWindowThreadProcessId Lib "user32.dll" _
           (ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer
    Declare Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Integer) As IntPtr

Public Sub EnsureProcessKilled(ByVal MainWindowHandle As IntPtr, ByVal Caption As String)
        ' for Excel versions <10, this won't be set yet
        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
      MainWindowHandle = FindWindow(Nothing, Caption)
        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
      Exit Sub ' at this point, presume the window has been closed.
        Dim iRes, iProcID As Integer
        iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
        If iProcID = 0 Then ' can’t get Process ID
            If EndTask(MainWindowHandle) <> 0 Then Exit Sub ' success
            Throw New ApplicationException("Failed to close.")
        End If
        Dim proc As System.Diagnostics.Process
        proc = System.Diagnostics.Process.GetProcessById(ProcessID)
        If proc.HasExited Then Exit Sub
    End Sub

Open in new window

Try the following method to forcefully release the memory being held by the excel object (make a call to the FlushMemory where you are currently calling GC.Collect() )
Imports System
Imports System.Runtime.InteropServices

Public Class MemoryManagement

    <DllImport("kernel32.dll", EntryPoint:="SetProcessWorkingSetSize", ExactSpelling:=True, CharSet:=CharSet.Ansi)> _
    Private Shared Function SetProcessWorkingSetSize(ByVal process As IntPtr, ByVal minimumWorkingSetSize As Integer, ByVal maximumWorkingSetSize As Integer) As Integer
    End Function

    Public Shared Sub FlushMemory()
        If Environment.OSVersion.Platform = PlatformID.Win32NT Then
            SetProcessWorkingSetSize(System.Diagnostics.Process.GetCurrentProcess().Handle, -1, -1)
        End If
    End Sub
End Class

Open in new window

ross13Author Commented:

Thanks for that. I added the code but the process still displays. I checked and the memory seems to keep going up for about another 15 seconds after the calls. Is it possible to get a pid for a created object and kill it?

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Yes. Try the following code to kill the process - you need the name of the process in the 'GetProcessedByName' call as shown below
Dim runningAcroProcesses As Process() = Process.GetProcessesByName("YOUR_PROCESS_NAME_HERE")
Dim er2 As IEnumerator = runningAcroProcesses.GetEnumerator()

Imports System.ServiceProcess;

While er2.MoveNext()
	Dim currProcess As Process = DirectCast(er2.Current, Process)
	If currProcess IsNot Nothing Then
	End If
End While

Open in new window

I forgot to mention - you will need to import the following namespace to kill the process
IMPORTS System.ServiceProcess
ross13Author Commented:
Thanks for the code snippit. It closes down excel as I specified “EXCEL.EXE” in the process name.
Is there a way of being able to uniquely identify the process that has been created by the objExcel = CreateObject("EXCEL.APPLICATION") via a PID / description etc.



ross13Author Commented:
The problem is that it closes down all excel documents so if the used has an open document then this could get terminated.

ross13Author Commented:
Got it to work. Thanks for you help.
How did you build the>> App. object

App.Caption = System.Guid.NewGuid.ToString.ToUpper
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.