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

Question

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.

What is the reasoning behind having so many sessions open? Why not fix that core issue instead of fixing a messy solution? There are very few reasons you would ever need more than one instance of Excel, and there's never a reason for *several* instances, as multiple workbooks can run in the same instance. — ashleedawg, Aug 03 '18 at 08:54
@ashleedawg, thanks for your comment. I've already fixed the main code and less possibility of remaining session, and it should be killed at the normal end and error capture section. But I'm still afraid there is possibility, like the case when the user shut down the program during the process and not caputer as errors. — Sachiko, Aug 03 '18 at 09:00

score 1 · Accepted Answer · answered Aug 03 '18 at 09:01

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.

Eric, thank you so much for your kind and useful information again!! That's exactlly what I wanted ... ! I'll verified it in our own system. — Sachiko, Aug 03 '18 at 09:06

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

1 Answers1