Automation Server Events Usage in VBA
 

I want to monitor file creation from an Excel workbook. For this I usually
use VBScript and WMI scripting library. The only VBA example I could find is
he

http://msdn2.microsoft.com/en-us/lib...20(VS.85).aspx

The sample code works when I put it in a UserForm module, but I want to use
it from a Class module. I found some information he

http://www.cpearson.com/excel/AppEvent.aspx

I created a Class module clsSink and set Instancing to PublicNotCreatable.
Based on the example, I put this code in clsSink:


Dim WithEvents sink As SWbemSink


Private Sub sink_OnObjectReady( _
ByVal objWbemObject As SWbemObject, _
ByVal objWbemAsyncContext As SWbemNamedValueSet)

On Error GoTo ErrorHandler

Sheets(1).Cells(1, 1) = _
objWbemObject.TargetInstance.Name
Sheets(1).Cells(2, 1) = _
objWbemObject.Path_.Class

Set objWbemObject = Nothing

Exit Sub ' Exit to avoid handler

ErrorHandler:

MsgBox "Error number: " _
& Str(Err.Number) & vbNewLine & _
"Description: " & Err.Description, _
vbCritical

End Sub

Private Sub Class_Initialize()

Dim services As SWbemServices
Dim strQuery As String
Dim cntxt As SWbemNamedValueSet

Set sink = New SWbemSink
Set services = GetObject("winmgmts:")

strQuery = _
"Select * From __InstanceOperationEvent " _
& "Within 3 " _
& "Where TargetInstance Isa 'Cim_DataFile' " _
& "And TargetInstance.Drive = 'C:' " _
& "And TargetInstance.Path = '\\scripts\\'"

services.ExecNotificationQueryAsync _
sink, strQuery, , , , cntxt

MsgBox "This Workbook will asynchronously " _
& "process Cim_DataFile events."

End Sub


In ThisWorkbook I put this code:


Public sink As clsSink

Private Sub Workbook_Open()
Set sink = New clsSink
End Sub


This code works, when a file is created, deleted, modified etc. in
c:\scripts, workbook receives those events.

This is the first time I use this in Excel VBA, so my questions a

Is this the right way to handle events from external automation objects?
Should I add or modify any of this code to make it more reliable (because it
is possible that the workbook will be open for a long time)?
Are there any samples that show how to use COM Automation server (any
automation object) events in Excel VBA?

Thanks in advance.

--
urkec