VB/VBA to read Task Manager

  • Thread starter Thread starter todtown
  • Start date Start date
T

todtown

I use Scheduled Tasks to open, update and close several workbooks each
day. Sometimes at the end of the day there will be an Excel instance
or two showing in the Processes tab of Task Manager. Is there a way I
can find out which workbook is associated with the open instance, or
get any information at all about this instance that might help me
discover which workbook may have caused the problem?

tod
 
Tod,

You can use the vbScript I've got between the lines below. It will find all
running processes named Excel.exe and send up a message box for each with
the exact date and time the process was created and the process ID for that
instance. With the start time, you should be able to figure out which
workbook spawned the instance of the application. If you want, you can
certainly place the code in a VBA module and place the results in different
cells (WScript.Echo won't work in VBA) but I'd probably just paste into
Notepad and save the text file with a vbs extension to run as a vbScript.

______________________________________

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'excel.exe'")

For Each objProcess in colProcessList
dtmStartTime = objProcess.CreationDate
strReturn = WMIDateStringToDate(dtmStartTime) & vbCrLf _
& " Excel instance with process ID " & objProcess.ProcessID & "
started."
Wscript.Echo strReturn
Next

Function WMIDateStringToDate(dtmStart)
WMIDateStringToDate = CDate(Mid(dtmStart, 5, 2) & "/" & _
Mid(dtmStart, 7, 2) & "/" & Left(dtmStart, 4) _
& " " & Mid (dtmStart, 9, 2) & ":" & _
Mid(dtmStart, 11, 2) & ":" & Mid(dtmStart, _
13, 2))
End Function

______________________________________

Steve
 
Not sure now where you can get it from, but there is a .dll file, written by
Edanmo Morgillo, tskschd.dll, that makes it easy to deal with the Windows
task scheduler in any way you want.
Do a Google for it or otherwise ask in
microsoft.public.vb.general.discussion

RBS
 
In case you didn't catch it, the newsgroup post above got very slightly
broken. In the segment,

strReturn = WMIDateStringToDate(dtmStartTime) & vbCrLf _
& " Excel instance with process ID " & objProcess.ProcessID & "
started."

The third line
started."
should be at the end of the line above it........Steve
 
Back
Top