First time posting so please let me know if any other information is required.

I'm trying to simplify a report template used in my office by writing a VBA-script that checks a certain Excel Workbook and automatically fills in the Word document. The scripting works fine and does what it's supposed to. However I noticed that when I run the script it fails to terminate the Excel.exe process in Task Manager. I've tried numerous solutions proposed here, in other forums and in Microsofts documentation but none seem to work for me. I gather it has to do with COM objects still existing when running the Application.Quit method but can't for the life of me figure out where those come from.

I've tried reducing the code down to it's most basic components and when running the below example it works fine and there's no excel.exe process still in task manager afterwards:

Private Sub Hämta_Click()

Dim XL As Excel.Application
Set XL = New Excel.Application

XL.Quit
Set XL = Nothing

End Sub

But as soon as I add to it, excel.exe keeps running in task manager after the macro is run, see example below:

Private Sub Hämta_Click()

Dim XL As Excel.Application
Set XL = New Excel.Application

Dim wkb As Excel.Workbook
Set wkb = XL.Workbooks.Open("C:\Example.xls")

wkb.Close (False)
Set wkb = Nothing

XL.Quit
Set XL = Nothing

End Sub

I have also tried this code with the same result:

Private Sub Hämta_Click()

Dim XL As Object
Set XL = CreateObject("Excel.Application")

Dim wkb As Object
Set wkb = XL.Workbooks.Open("K:\Uppdrag.xls")

wkb.Close (False)
Set wkb = Nothing

XL.Quit
Set XL = Nothing

End Sub

The above two macros also keep creating instances of Excel.exe which are not closed.

Am I doing something wrong when declaring the wkb object or is there some other reason for it not working? I've seen examples where code snippets are included that kills the process via the task manager, but I don't understand the reason for the above not working. The only other workaround I've found so far is to not include the XL.Quit method at all and instead set XL.Visible = True and let the user manually close the window afterwards and though I'm not really happy with this it at least mitigates the risk of accumulating processes in the Task Manager that hogs a lot of memory.

Any suggestions to what might be causing this and how to avoid it would be much appreciated! Again, first time asker so please let me know if any clarification is needed.

CodePudding user response：

Based on the comments it does not seem to be possible to find the root cause why the newly created excel instance cannot be finished in a "normal" way.

Based on the code here one can just kill the process

Option Explicit
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
Private Declare Function AdjustTokenPrivileges Lib "advapi32.dll" (ByVal TokenHandle As Long, ByVal DisableAllPrivileges As Long, NewState As TOKEN_PRIVILEGES, ByVal BufferLength As Long, PreviousState As TOKEN_PRIVILEGES, ReturnLength As Long) As Long
Private Declare Function OpenProcessToken Lib "advapi32.dll" (ByVal ProcessHandle As Long, ByVal DesiredAccess As Long, TokenHandle As Long) As Long
Private Declare Function LookupPrivilegeValue Lib "advapi32.dll" Alias "LookupPrivilegeValueA" (ByVal lpSystemName As String, ByVal lpName As String, lpLuid As LUID) As Long
Private Declare Function GetCurrentProcess Lib "kernel32" () As Long

Private Type LUID
    LowPart As Long
    HighPart As Long
End Type

Private Type LUID_AND_ATTRIBUTES
    pLuid As LUID
    Attributes As Long
End Type

Private Type TOKEN_PRIVILEGES
    PrivilegeCount As Long
    TheLuid As LUID
    Attributes As Long
End Type

Function ProcessTerminate(Optional lProcessID As Long, Optional lHwndWindow As Long) As Boolean
    Dim lhwndProcess As Long
    Dim lExitCode As Long
    Dim lRetVal As Long
    Dim lhThisProc As Long
    Dim lhTokenHandle As Long
    Dim tLuid As LUID
    Dim tTokenPriv As TOKEN_PRIVILEGES, tTokenPrivNew As TOKEN_PRIVILEGES
    Dim lBufferNeeded As Long
    
    Const PROCESS_ALL_ACCESS = &H1F0FFF, PROCESS_TERMINATE = &H1
    Const ANYSIZE_ARRAY = 1, TOKEN_ADJUST_PRIVILEGES = &H20
    Const TOKEN_QUERY = &H8, SE_DEBUG_NAME As String = "SeDebugPrivilege"
    Const SE_PRIVILEGE_ENABLED = &H2

    On Error Resume Next
    If lHwndWindow Then
        'Get the process ID from the window handle
        lRetVal = GetWindowThreadProcessId(lHwndWindow, lProcessID)
    End If
    
    If lProcessID Then
        'Give Kill permissions to this process
        lhThisProc = GetCurrentProcess
        
        OpenProcessToken lhThisProc, TOKEN_ADJUST_PRIVILEGES Or TOKEN_QUERY, lhTokenHandle
        LookupPrivilegeValue "", SE_DEBUG_NAME, tLuid
        'Set the number of privileges to be change
        tTokenPriv.PrivilegeCount = 1
        tTokenPriv.TheLuid = tLuid
        tTokenPriv.Attributes = SE_PRIVILEGE_ENABLED
        'Enable the kill privilege in the access token of this process
        AdjustTokenPrivileges lhTokenHandle, False, tTokenPriv, Len(tTokenPrivNew), tTokenPrivNew, lBufferNeeded

        'Open the process to kill
        lhwndProcess = OpenProcess(PROCESS_TERMINATE, 0, lProcessID)
    
        If lhwndProcess Then
            'Obtained process handle, kill the process
            ProcessTerminate = CBool(TerminateProcess(lhwndProcess, lExitCode))
            Call CloseHandle(lhwndProcess)
        End If
    End If
    On Error GoTo 0
End Function

And you just use the code like that

Sub TestIt()

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    
    ' Do something with xlApp
        
    
    'Terminate the process
    ProcessTerminate , xlApp.hwnd
End Sub