vba check if specific process task id is still running

Question

I am using the Shell() function to execute an external application in MS Access. Shell() returns a task ID of the specific process that was started.

I need a way to check if that process is still running or if it has closed. I'm sure that I will need to do some sort of windows API call but I can't seem to find anything just now.

Answer 1

This is an Access FAQ, and it was answered years and years ago on the FAQ site for comp.databases.ms-access, http://mvps.org/access/.

API: Shell and Wait

Answer 2

Shell Tasklist (Description at http://technet.microsoft.com/en-us/library/bb491010.aspx) is a good way to do it manually. I'm not sure how you could interact with the list of PIDs by automation, though. That's a bit more difficult.

Tip: If you just want to kill a process, use Shell "TaskKill /F /IM ""excel.exe""" to kill all open instances of MS Excel. Or Shell "TaskKill /F /IM ""msaccess.exe""" to kill all open instances of MS Access (including the Access file that hosts your VBA script, unfortunately). This is how I end my standard issue Error Handler.

Edit

When you run it, you should something like:

Answer 3

I used the following as part of the WMI:

Public Function WaitForProcess(ProcessName As String, Optional ProcessID As Boolean = False)

    ' if you dont mind including the reference for Microsoft WMI Scripting V.1.2 Library, uncomment below
    ' Otherwise, it should work fine without the reference, just no intelasense

    UpdateStatus "Starting WaitForProcess function to wait for process: " & ProcessName

    Dim strComputer As String
    Dim colProcesses
    Dim objWMIService

    UpdateStatus "Use this computer to see processes on"
    strComputer = "."

    UpdateStatus "Impersonate this computer"
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    UpdateStatus "Get all running processes with a " & IIf(ProcessID = False, "Name", "ProcessID") & " of: " & ProcessName
    Set colProcesses = objWMIService.ExecQuery("SELECT * " & _
                                               "FROM Win32_Process  " & _
                                               "WHERE " & IIf(ProcessID = False, "Name", "ProcessID") & " = '" & ProcessName & "'")

    UpdateStatus "Waiting untill there are no more instances of this process shown in the process list"
    Do
        DoEvents
        ' Update the collection of processes returned

        Set colProcesses = objWMIService.ExecQuery("SELECT * FROM Win32_Process WHERE " & IIf(ProcessID = False, "Name", "ProcessID") & " = '" & ProcessName & "'")


    Loop Until colProcesses.Count = 0

    UpdateStatus "The " & IIf(ProcessID = False, "Process Name", "Process ID") & ": " & ProcessName & " concluded successfully. WaitForProcess Completed"

End Function

This works well for my project; however, it does require Windows Management Instrumentation.