Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Animating the Microsoft Office Assistant (Part 1)

Note: The Office Assistant has been removed from Office 2007, and therefore this script will not work on that version of Microsoft Office.

You know, life is full of guilty pleasures, those things that we all do and those things that we all like, but those things that we will never own up to. For example, some 8.4 billion TV dinners have been sold since their invention, yet apparently nobody in the world has ever purchased or eaten one. Millions of people watch soap operas every day, even though nobody watches soap operas. Hamburger Helper? Canned soup? Pre-packaged salad? Perish the thought.

The computer world has its own share of guilty pleasures (Windows Solitaire, anyone?), including the animated Assistant found in the Microsoft Office applications:

Yes, we know: you don’t like the Office Assistant and you’d never use it. No sir, no way…well, unless maybe you could script the Office Assistant. Not that you would find it useful, of course. But your, uh … users … might find it useful. Yeah, that’s right: your users might get some use out of the Officer Assistant. And the pre-packaged salad? That’s, uh, that’s for those same users ….

Actually, this is a pretty good excuse: your users might very well find the Office Assistant useful. Although you can create a rudimentary dialog box using VBScript’s Msgbox function, that dialog box isn’t going to be very fancy. Can you display custom graphics in that message box? Can you change the font size or the font color? Can you provide check boxes that allow a user to select multiple options from a single dialog box? No, no, and no.

But guess what: you can do all those things – and more – using the Office Assistant. We can’t cover all these capabilities today, but we will introduce the Office Assistant and show you how to create a basic word balloon dialog box. Later on, we’ll show you how to do such things as display custom graphics, change the font size and font color, and even provide check boxes that allow you – uh, allow a user – to select multiple options from a single dialog box. And if there’s time, we’ll even show you – uh, show your users – how they can make a pretty good chicken casserole using canned soup.

For starters, here’s a script that starts up Microsoft Excel. After Excel starts, the default animated assistant (on this computer, that happens to be Peedy the Parrot) appears and asks if you want to open the last spreadsheet you were working on. Peedy asks the question in the form of a dialog box like this:

If you click Yes, the script opens the last spreadsheet you were working on; if you click No, then the script creates a new, blank worksheet for you. (Note that if the Office Assistant isn’t installed, the user will be prompted to install it when this script is run.) Here’s what the script looks like:

Const msoBalloonButtonYes = -3 
Const msoButtonSetYesNo = 4

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 

objExcel.Assistant.On = True
objExcel.Assistant.Visible = True 
objExcel.Assistant.Sounds = True

Wscript.Sleep 2000

Set objBalloon = objExcel.Assistant.NewBalloon 
objBalloon.Heading = "Welcome to Excel"
objBalloon.Button = msoButtonSetYesNo 
objBalloon.Text = "Would you like to open your last spreadsheet?" 

If objBalloon.Show = msoBalloonButtonYes Then 
    objExcel.RecentFiles(1).Open
Else
    Set objWorkbook = objExcel.Workbooks.Add()
    Set objWorksheet = objWorkbook.Worksheets(1)
End If       

objExcel.Assistant.Visible = False
objExcel.Assistant.On = False

So how do we get Peedy to respond to our every wish (well, assuming our every wish involves opening or not opening the last spreadsheet we were working on)? To begin with, we define a constant named msoBalloonButtonYes and set the value to -3. We’ll use this later on in the script to determine whether or not the user clicked the Yes button. If we wanted to determine whether the user clicked the No button we’d create a constant named msoBalloonButtonNo and set the value to -4. For a complete list of button types and their associated values, click here and take a look at the msoBalloonButtonType enumeration.

In addition to msoBalloonButtonYes, we also create a constant named msoButtonSetYesNo and set the value to 4; this results in a word balloon dialog box that features a Yes button and a No button. And of course you can have different button sets in your dialog box; for more information, click here and take a look at the msoButtonSetType enumeration. (And don’t fear: everyone’s favorite – Abort, Retry, Ignore – is one of the options available to you.)

Next we create an instance of the Excel.Application object and set the Visible property to True; that gives us a working instance of Microsoft Excel that we can see onscreen. We then use these lines of code to activate the Office Assistant, make said Assistant visible, and turn on the sound effects:

objExcel.Assistant.On = True
objExcel.Assistant.Visible = True 
objExcel.Assistant.Sounds = True

Following that we use the Sleep method to pause the script for two seconds (2000 milliseconds); we do this simply to give the character time to make an appearance. We then use these four lines of code to configure the word balloon dialog box:

Set objBalloon = objExcel.Assistant.NewBalloon 
objBalloon.Heading = "Welcome to Excel"
objBalloon.Button = msoButtonSetYesNo 
objBalloon.Text = "Would you like to open your last spreadsheet?"

We start off by creating an instance of the NewBalloon object. We then set the following properties of this new word balloon:

Now it’s time to display the word balloon dialog, something we do by calling the Show method. This line of code displays the word balloon and then waits for the user to click one of the two buttons:

If objBalloon.Show = msoBalloonButtonYes Then

If the user clicks Yes (msoBalloonButtonYes) the script opens the spreadsheet that the user was last working with. How does the script know which spreadsheet that was? That’s easy: the last spreadsheet to be used is the first item in the RecentFiles collection. This line of code opens the most recently-used file:

objExcel.RecentFiles(1).Open

And if the user doesn’t click Yes? Well, in that case the script uses these two lines of code to create a new workbook and a new worksheet:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

Finally, we dismiss our character and turn off the Office Assistant by using these two lines of code:

objExcel.Assistant.Visible = False
objExcel.Assistant.On = False

Not bad, huh? But, as we noted earlier, it’s possible to do even more with the Office Assistant than what we’ve shown you; for example, you can create a word balloon dialog that includes a number of check boxes. Users can then select as many (or as few) check boxes as they desire, and the script will then carry out the appropriate actions. That’s more than we can talk about today, but we’ll revisit the Office Assistant sometime soon, and show you what else you can do to create character-driven dialog boxes.

Yes, sorry: show you what else you can do to create character-driven dialog boxes for your users. Not for you, though. Never for you. No sir, no way ….