Using VBScript to monitor Office events…or not

I spent an unexpectedly long time trying to figure out how to monitor Office events using VBScript and this post shares my experience, largely disappointing. The below scenarios were tested with Vista Ultimate and Office 2007 as well as with Windows 7 Ultimate and Office 2010 Beta.

This is about using VBScript through the Windows Script Host (WSH) — not VB6, not VBA, not VB.Net, and not VBScript in a browser — to sink Office application events.

For the longest time I was under the impression that there was no way to write event procedures in WSH-based VBScript. It turns out that VBScript (either in a .VBS file or in a .WSF file) running through the WSH does have a few different ways of monitoring events raised by programs it can connect to. These are documented in Scripting Events

Essentially, there are two ways to connect procedures with events.

  • The first way to connect a procedure with an event is to inform the WSH as to the prefix used in the names of the event procedures. This in turn can be done in two ways.

    • The first is to use the WScript CreateObject (or GetObject) methods. While named the same as the VB CreateObject and GetObject functions, these two methods include an additional argument. This string argument tells the WSH the prefix of the event procedures names. An example of the GetObject method is

         set anObj=wscript.getobject(“”,“powerpoint.application”,“Obj_”)
    • The second technique to inform the WSH of the prefix of the event procedures names is to use the ConnectObject method. This is also documented in the Scripting Events reference above.
  • The second way to connect a procedure with an event is by declaring an object capable of raising events. The ID of the object becomes the prefix of the event procedure. The declaration in a WSF file looks something like

    <job>
    <object progid=“word.application” id=“myorder” events=“true”/>
    </job>

Since my primary intent was to monitor Excel events, I went about it using the first approach. I tested with…as you can imagine just about every variation and combination thereof I could think of…both the CreateObject and the GetObject methods and discovered neither worked. I tried the ConnectObject method and it resulted in a run time error — something about an error with CreateObject. Of course, I thought I was doing something wrong and I spent who knows how many hours testing, retesting, Googling, testing, and retesting.

Finally, I gave up and decided to use the object declaration approach. That too did not work! After more struggling with tests, retests, Google searches, tests, and retests, I figured I was doing things correctly and decided to test Word.

Well, Word worked with the Object declaration approach but not the CreateObject/GetObject/ConnectObject methods!

Just to round out the tests, I tested PowerPoint. It worked with the GetObject method but not the Object declaration!

So, bottom line. None of the documented methods for monitoring events in VBScript seems to work with Excel. One can monitor Word events only with the object declaration approach and one can monitor PowerPoint events only with the GetObject approach.

Maybe, I missed something — and if so someone please tell me what I did wrong — but as of now it has been several days of all sorts of frustration.

The two approaches that work:

Track PowerPoint events in a VBS file:

option explicit
dim anObj, aDoc, aDoc2
sub Obj_NewPresentation(byval WB)
    msgbox “In NewPresentation: “ & wb.name
    end sub

sub testEvent()

    set anObj=wscript.getobject(“”,“powerpoint.application”,“Obj_”)
    anObj.visible=true
    set aDoc = anObj.presentations.add()
    set aDoc2 = anObj.presentations.add
    dim I
    for I=1 to 5
        wscript.sleep 1000
        next
    aDoc.close
    aDoc2.close
    anObj.quit
    end sub
‘msgbox wscript.version
testEvent

It is also possible to respond to Word events with the following in a WSF file:

<job>
<object progid=“word.application” id=“myorder” events=“true”/>
<script language=“vbscript”>
sub myorder_NewDocument(byval WB)
   Wscript.echo “In NewDocument: “ & wb.name
end sub
myorder.visible=true
myorder.documents.add
myorder.documents.add
wscript.sleep 5000
myorder.quit
</script>
</job>

The following four do not work. In each case, the application starts up, two new files open, and after the designated interval close and the application quits. However, the event procedures are not called.

Respond to an Excel event in a VBS file:

option explicit
dim xlObj, xlWB, xlWB2
sub Obj_NewWorkbook(byval WB)
    msgbox “In newWorkbook: “ ‘& wb.name
   end sub
sub Obj_Calculate()
    msgbox “In Calculate” ‘& wb.name
   end sub

sub testEvent()

    set xlObj=wscript.getobject(“”,“excel.application”,“Obj_”)
    xlobj.visible=true
    ‘call wscript.connectobject (xlObj,”Obj_”)
   set xlwb=xlobj.workbooks.add()
    set xlWB2 = xlobj.workbooks.add
    xlwb2.sheets(1).cells(1,1).value=1
    xlwb2.sheets(1).cells(1,2).formula=“=A1+1”
    dim I
    for I=1 to 5
        wscript.sleep 1000
        next
    xlwb.close false
    xlWB2.close false
    xlobj.quit
    end sub
‘msgbox wscript.version
testEvent

Respond to Word events in a VBS file also does not work.

dim xlObj, xlWB, xlWB2
sub xlObj_NewDocument(byval WB)
    wscript.echo “In newWorkbook: “ ‘& wb.name
   end sub

sub testEvent()
    ‘set xlObj=wscript.getobject(“”,”word.application”,”xlObj”)
   set xlObj=wscript.getobject(“”,“word.application”,“xlObj_”)
    xlobj.visible=true
    xlobj.documents.add
    xlobj.documents.add
    wscript.sleep 3000

    xlobj.quit
    set xlobj=nothing
    end sub
sub testEvent2()
    set xlObj=createobject(“word.application”)
    ‘wscript.connectobject xlObj,”xlObj”
   wscript.connectobject xlObj,“xlObj_”
    xlobj.visible=true
    xlobj.documents.add
    xlobj.documents.add
    wscript.sleep 3000

    xlobj.quit
    end sub
testEvent
testEvent2

Respond to Excel events in a WSF file does nothing:

<job>
<object progid=“excel.application” id=“myorder” events=“true”/>
<script language=“vbscript”>
sub myorder_NewWorkbook(byval WB)
    Wscript.echo “new order received myorder “ & wb.name
    end sub
myorder.visible=true
myorder.workbooks.add
myorder.workbooks.add
wscript.sleep 3000
myorder.quit
</script>
</job>

and finally, responding to PowerPoint events in a WSF file also does not work.

<job>
<object progid=“powerpoint.application” id=“myorder” events=“true”/>
<script language=“vbscript”>
sub myorder_NewPresentation(byval WB)
   Wscript.echo “In NewDocument: “ & wb.name
end sub
myorder.visible=true
myorder.presentations.add
myorder.presentations.add
wscript.sleep 5000
myorder.quit
</script>
</job>
Posted in Uncategorized

4 thoughts on “Using VBScript to monitor Office events…or not

  1. I now this is years after the Post, but maybe someone needs an answer.
    The scripts doesn’t work because it quits after sleeping.
    ‘At Top of Script
    Dim bRunning
    bRunning = True

    ‘Code Handling Events

    At End of Script
    While bRunning
    wscript.sleep 3000
    DoEvents
    Wend

  2. Contribution:
    I know it’s even 2 years later than the last post, but the subject is still of interest: The wend loop suggested by PJK seems to NOT be working. Has it been tried and made to work ? If yes, it would be a good idea to post the working code. In my case, the loop, neither in a vbs file nor in a wsf file, did not help capturing the firing of the Excel event. And by the way, DoEvents is not recognised by vbScript, which is what made me wonder if the loop had ever been tried before posting.

    Confirmation:
    I can confirm that it was easy to check that the powerpoint example worked though.

    Improvement:
    The attempt at sub Obj_Calculate() seems to fail because _calculate() is not an event, is it just a method? However, I succeeded in running a similar _AfterCalculate (without parenthesis). At that point, it is exhilarating to see an Excel event firing msgboxes back in the vbs
    calling script … The limitation of my finding is that any other Excel event that handles parametres (wb or ws or target …) does not seem to work (meaning fire back in vbs)

    Further needs:
    1- If anyone has got an insight about how to get other Excel Application events that have ByVal parametres to fire back into vbs, there are obviously still some folks interested in the solution …
    2- if anyone knows how to connect to other excel objects (like sheets) from the vbs script to listen to their events, same thing, there is a lot of interest

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *