Search code examples
excelms-accesssessionpid

Terminate All Remaining Excel Sessions, only created by Access VBA Procedure

Is there any way to distinguish EXCEL Session created by MS-Access VBA, from other EXCEL Sessions which user has created?

Currently I terminated Excel session by its processe id, explicitly at the end of procedure. Since they can be remained, even though that instance is sure to be closed.

To avoid any remaining Excel sessions, I'd like to terminate all other Excel sessions created by MS-Access VBA, but not include the user's Excel Sessions.

Here's current scripts just for your reference. We can terminate all excel sessions by #2 sub procedure, but can be included user sessions though.

-- #1 Check Process ID and Terminate at the end of Procedure

Public Declare Function GetWindowThreadProcessId Lib "user32" _
  (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As Long

'Create New Instance
Set objExcel = New Excel.Application

'Get ProcessID of Excel Instance, Opened in this procedure.
Dim ProcXLID
Dim CurrentThreadID

ProcXLID = 0
CurrentThreadID = GetWindowThreadProcessId(objExcel.hwnd, ProcXLID)
Debug.Print "Current Excel Instance Handle : " & objExcel.hwnd & " ProcessID : " & ProcXLID

～ Export Excel Reports ～

'Close instance
objExcel.Quit
Set objExcel = Nothing

'Terminate own Excel Session if remains
ExcelProcess_kill ProcXLID

-- #2. Procedues Terminate Excel Sessions

Sub ExcelProcess_kill(ByVal ProcID As Integer)

On Error Resume Next

    Dim objProcList
    Dim objProcess

    Dim StrProcName
    StrProcName = "EXCEL.EXE"

    Dim SessionCnt As Long
    SessionCnt = 0

    Set objProcList = GetObject("winmgmts:").InstancesOf("win32_process")

    'For Named Excel Process -- ExcelProcess_kill(pid)
    If ProcID > 0 Then

        For Each objProcess In objProcList
            If LCase(objProcess.Name) = StrProcName And LCase(objProcess.ProcessID) = ProcID Then
                Debug.Print "Terminate Session Info."; objProcess.Name; objProcess.ProcessID
                objProcess.Terminate
                SessionCnt = SessionCnt + 1
            End If
        Next

    'For All Excel Process -- ExcelProcess_kill(0) 
    Else

        For Each objProcess In objProcList
            If LCase(objProcess.Name) = StrProcName Then
                Debug.Print "Terminate Session Info."; objProcess.Name; objProcess.ProcessID
                objProcess.Terminate
                SessionCnt = SessionCnt + 1
            End If
        Next

    End If

    Debug.Print "Terminated Session Cnt ：" & SessionCnt

End Sub

Any advice would be highly appreciated again.

Solution

  • An easy way to determine if an Excel application is opened by the user is by using the Application.UserControl property, which is true for applications opened by the user, but false for applications opened by using VBA.

    You can get a list of the application objects for all running Excel applications by using the code found in this answer. Then you can kill them using your existing code.

    Note that Excel applications opened by other programs will get terminated as well. There's no way to differentiate between which COM program opened the Excel application.

    Also note that making the Excel Application visible sets this property to true. But that's probably wanted, because a user might open other workbooks in a visible application instance you've created, and killing that might cause the user to lose work.