Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Office 2000 Automation Events. | Excel Programming | |||
object does not source automation events | Excel Programming | |||
automation server/add-in installation | Excel Programming | |||
very high cpu/ram usage: many Excel/SQL Server ADO return-trips | Excel Programming | |||
Automation Events | Excel Programming |