Daily Dose of Excel

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:

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

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:

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

Respond to Excel events in a WSF file does nothing:

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