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 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
-
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:
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:
<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:
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.
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:
<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.
<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>
Tushar, I’m curious what you were going to do with this if successful.
I am assuming he was trying to create an Excel log file.
EM
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
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