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
here:
http://msdn2.microsoft.com/en-us/library/aa390420(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 here:
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 are:
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
Similar Threads
