VBA code to monitor windows process displayed in task manager

VBA code to monitor windows process displayed in task manager.

I am trying to get the RAM usage and CPU usage of an application and add those values in excel.

i tried using WMI class as below but howerver i am getting process ID.I am unable to retrieve RAM usage and CPU usage. Can anyone help me in this?

Sub test2()
    Set objWMIService = GetObject("winmgmts:\\.\root\CIMV2")
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Process where caption='excel.exe'", , 48)
    For Each objItem In colItems
        Sheet1.Range("d2").Value = objItem.ProcessId
    Next

    Set colItems = objWMIService.ExecQuery( _"SELECT * FROM Win32_PerfFormattedData_PerfProc_Process where IDProcess=" & Sheet1.Range("d2").Value, , 48)

    For Each objItem In colItems
        Sheet1.Range("A1").Value = "PercentProcessorTime: " & objItem.PercentProcessorTime
    Next

End Sub

Solution

If you choose to go the WMI route, then you might be after the WorkingSetSize property of the Win32_Process class:

WorkingSetSize Data type: uint64 Access type: Read-only Qualifiers: DisplayName ("Working Set Size"), Units ("bytes") Amount of memory in bytes that a process needs to execute efficiently—for an operating system that uses page-based memory management. If the system does not have enough memory (less than the working set size), thrashing occurs. If the size of the working set is not known, use NULL or 0 (zero). If working set data is provided, you can monitor the information to understand the changing memory requirements of a process.

And the PercentProcessorTime of the Win32_PerfFormattedData_PerfProc_Process class:

PercentProcessorTime Data type: uint64 Access type: Read-only Qualifiers: CookingType ("PERF_100NSEC_TIMER") , Counter ("PercentProcessorTime") , PerfTimeStamp ("TimeStamp_Sys100NS") , PerfTimeFreq ("Frequency_Sys100NS") Percentage of time that the processor is executing a non-idle thread. This property was designed as a primary indicator of processor activity. It is calculated by measuring the time that the processor spends executing the thread of the idle process in each sample interval, and subtracting that value from 100%. (Each processor has an idle thread which consumes cycles when no other threads are ready to run.) It can be viewed as the percentage of the sample interval spent doing useful work. This property displays the average percentage of busy time observed during the sample interval. It is calculated by monitoring the time the service was inactive, and then subtracting that value from 100%.

But you might also want to look at the SWbemRefresher object (https://msdn.microsoft.com/en-us/library/aa393838(v=vs.85).aspx).

Skeleton VBA code for you to work with:

Dim srvEx As SWbemServicesEx
Dim xlProcSet As SWbemObjectSet
Dim xlPerfSet As SWbemObjectSet
Dim objEx As SWbemObjectEx

Set srvEx = GetObject("winmgmts:\\.\root\CIMV2")
Set xlProcSet = srvEx.ExecQuery("SELECT * FROM Win32_Process WHERE name = 'EXCEL.EXE'")
Set xlPerfSet = srvEx.ExecQuery("SELECT * FROM Win32_PerfFormattedData_PerfProc_Process WHERE name = 'EXCEL'")

For Each objEx In xlProcSet
    Debug.Print objEx.Name & " RAM: " & objEx.WorkingSetSize / 1024 & "kb"
Next

For Each objEx In xlPerfSet
    Debug.Print objEx.Name & " CPU: " & objEx.PercentProcessorTime & "%"
Next