How to montiro cpu useage for a process in excel - vba

Matt_Hirst · 19 Dec 2009 at 10:20

I have a spreadsheet that needs to call a data processing tool - all very simple. However I want excel to stop processing whilst the data processing tool is running and then recommence ones its finished.

This should be all very simple but the data processing tool once finished prompts for the enter key to be pressed, thus from an excel point of view the process never ends until this enter key is pressed.

So how do I get excel to monitor the process that is running , specificly the cpu processing, thus when it drops to nothing cause its sat waiting I can then send the enter key and force the process to finish.

Hope someone can help.

regards,

Matt

P.S. Forgot to add its excel 2003.

dangerstat · 19 Dec 2009 at 10:55

You probably don't need to monitor the CPU at all,when you launch the app with something like
Dim RetVal
RetVal = Shell("C:\WINDOWS\CALC.EXE", 1) ' Run Calculator.

just loop checking the return value ?

Matt_Hirst · 19 Dec 2009 at 11:37

Hi,

I thought the RetVal would only show current state i.e program is running or program is not running? Obviously whilst the program is waiting for the enter key it is still running, thus no chnage?

regards,

Matt

dangerstat · 19 Dec 2009 at 12:01

could you read the process details from pmon?

Matt_Hirst · 19 Dec 2009 at 12:48

Not sure on that?

regards,

Matt

dangerstat · 19 Dec 2009 at 13:13

Matt_Hirst said:
Not sure on that?

regards,

Matt

Well "Google" tells me pmon is pretty much the equivilent of "top" on linux - this displays information on running processes (including CPU utilization). If so you'll want to read the output from pmon and parse it.

wozencl · 19 Dec 2009 at 13:15

Use WMI, W32 Process

http://msdn.microsoft.com/en-us/library/aa394599%28VS.85%29.aspx

MSDN is your friend.

EDIT: Should come in handy
http://msdn.microsoft.com/en-us/library/aa394597(VS.85).aspx

Matt_Hirst · 21 Dec 2009 at 11:01

Must admit you both lost me with your responses :confused:

regards,

Matt

wozencl · 21 Dec 2009 at 16:23

Something like the below should do it, apologies it was a rush job and my keyboard is fudged.

Code:

 Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub WaitProcess()
' Wait for Winzip example

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & strComputer & "\root\cimv2")
Set PerfProcess = objWMIService.Get("Win32_PerfFormattedData_PerfProc_Process.Name='[B]WINZIP32[/B]'")

' Adjust accordingly - 25 secs is fine for my zip extraction
iMaxCounter = 25

' Get current usage
iCurrentProcUse = PerfProcess.PercentProcessorTime
If iCurrentProcUse = 0 Then
    MsgBox "Process is not running or does not exist", vbInformation
    Exit Sub
End If

While (iCurrentProcUse > 0)
    If i < iMaxCounter Then
        iCurrentProcUse = PerfProcess.PercentProcessorTime
        PerfProcess.Refresh_
        SillySleep (1)
    i = i + 1
    Else
        MsgBox "Process did not finish in time.", vbExclamation
        Exit Sub
    End If
Wend

MsgBox "Process completed. Time waited: " & i & " seconds.", vbInformation

End Sub


Sub SillySleep(iSeconds)
 Do Until i = iSeconds * 1000
    ' Do Nothing
     Sleep 1000
     i = i + 1000
 Loop
End Sub

Matt_Hirst · 4 Jan 2010 at 09:08

Finally got back to work and can try the code out...

Have put the code into a blank worksheet with just a button on, when the button is pressed it calls the waitprocess sub. Now when it is running I get an error message that just says automation error and fails at the line

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

What have I done wrong?

Do I need to include an yadditional references?

regards,

Matt