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.

Calling a File Open Dialog Box from Microsoft Word

On the old TV show MacGyver our hero (MacGyver) routinely used simple household items like a paperclip to do everything from hotwire a truck to defuse a bomb. (He couldn’t manage to get the series renewed for an eighth year, however, suggesting that there are some things that even paperclips can’t do.) Granted, a paperclip might not have been MacGyver’s tool of choice (“Bomb defuser? No thanks; just give me a paperclip.”), but when faced with a ticking time bomb you tend to put elegance aside and use anything and everything at your disposal.

The same thing tends to be true of system administration scripting: when the need arises, you put elegance aside and use anything and everything at your disposal. For example, take the seemingly-simple task of displaying a File Open dialog box to users. If you’re running on Windows XP this is easy: you can write a script that takes advantage of a File Open dialog box included in the operating system. (What do you mean, “No one told me you could do that”? Didn’t you read this Hey, Scripting Guy! column?)

Unfortunately, though, this solution – while very elegant – applies only to Windows XP: the dialog box in question isn’t available on Windows 2000, Windows 98, Windows NT 4.0, or even on Windows Server 2003. So unless you’re running Windows XP you’re just plain out of luck, right?

Shame on you: would MacGyver give up that easily? Well, to be honest, we never actually watched the show, so we can’t say for sure: maybe he would give up that easily. But we doubt it. Instead, we’re willing to bet that MacGyver would find a paperclip, straighten it out, bend it into the shape of a U, and then use a script like this one to display a File Open dialog box:

Const msoFileDialogOpen = 1

Set objWord = CreateObject("Word.Application")

objWord.ChangeFileOpenDirectory("C:\Scripts")

objWord.FileDialog(msoFileDialogOpen).Title = "Select the files to be deleted"
objWord.FileDialog(msoFileDialogOpen).AllowMultiSelect = True

If objWord.FileDialog(msoFileDialogOpen).Show = -1 Then
    objWord.WindowState = 2
    For Each objFile in objWord.FileDialog(msoFileDialogOpen).SelectedItems
        Wscript.Echo objFile
    Next 
    
End If

objWord.Quit

Editor’s Note. Not all the Scripting Guys spend their lives watching ESPN; some of us have seen the show MacGyver. And if we weren’t so busy working all the time we’d know that it’s on every Monday through Friday at 4:00.

Before we go any further we should point out that, as far as we know, you don’t actually need the paperclip in order to make this script work; however, you do need to have Microsoft Word installed. That’s because the script creates an instance of Microsoft Word and then uses the File Open dialog box found in Microsoft Office. In some ways this might seem like overkill: starting up an instance of Microsoft Word just so you can display a File Open dialog box. But, like using a candy bar to stop a sulfuric acid leak, you do what you have to do.

Note. That’s how you know that MacGyver was a fictional series. Any real person would have eaten the candy bar first and only then started worrying about the sulfuric acid.

Let’s take a closer look at our script and how it works. We begin by defining a constant named msoFileDialogOpen and setting the value to 1; this tells Word that we want to work with the File Open dialog box. (Does that mean that there are other dialog boxes you can work with? You bet it does: take a look at the Microsoft Word VBA Language Reference for more information.) We create an instance of the Word.Application object and then call this optional line of code:

objWord.ChangeFileOpenDirectory("C:\Scripts")

This code tells Word that when we display the File Open dialog box we’d like it to start off by showing the files in the folder C:\Scripts. If we didn’t specify a folder then, by default, File Open would start off by displaying files in the System32 folder (e.g., C:\Windows\System32).

Next we use these two lines of code to configure property values for our dialog box:

objWord.FileDialog(msoFileDialogOpen).Title = "Select the files to be deleted"
objWord.FileDialog(msoFileDialogOpen).AllowMultiSelect = True

As both you and MacGyver have already figured out, the first line simply specifies the caption (or title) for the dialog box; our File Open dialog box will display the caption Select the files to be deleted. The second line of code gives us the ability to select multiple files in the dialog box (using the good old hold-down-Ctrl-key-and-click-on-multiple-files method).

We then use this line of code to display the File Open dialog box and wait for the user to click either Open or Cancel:

If objWord.FileDialog(msoFileDialogOpen).Show = -1 Then

You’re right: this line of code does need a little more explanation, doesn’t it? What we’re doing here is using the Show method to display the File Open dialog box. By default, the dialog box will remain onscreen until the user dismisses it. As you know, there are three ways to dismiss a File Open dialog box: click the Open button (which, in this cases, returns a value of -1); click the Cancel button (which returns a value of 0); or click the Close button (which returns a value of -2). Our line of code waits until the dialog box is dismissed; when it is, the script checks to see if the return value is -1 (meaning the user clicked Open). If the return value is not -1 the script skips the If-Then block and uses this line of code to terminate our instance of Microsoft Word:

objWord.Quit

But what if the user did click Open? In that case, we loop through the collection of selected items (remember, this is a multi-select dialog box) and then echo back the path of each selected file:

For Each objFile in objWord.FileDialog(msoFileDialogOpen).SelectedItems
    Wscript.Echo objFile
Next

Needless to say, you could do something more interesting than simply echo back the file paths. But that’s up to you.

Incidentally, inside our If-Then block we also use this line of code to deal with a minor annoyance related to this script:

objWord.WindowState = 2

As you might have noticed at the beginning of this script, we never set the Visible property to True; that’s because we want Word to run in an invisible window. However, after you click a button in the dialog box Word becomes visible whether you want it to or not. By setting the WindowState property to 2 we cause Word to minimize itself and thus get out of the way. You might see a momentary flash as Word becomes visible and then minimizes itself, but we decided that was better than having an “empty” instance of Microsoft Word running in the background. If you don’t like this momentary flash, then simply remove that line of code.

Note. As far as we know there’s no way to prevent Word from showing itself; like we said, workarounds involve sacrificing elegance.

So what happens when you actually run this script? Well, you should see a File Open dialog box that looks an awful lot like this one:

Granted, it’s not the same thing as taking industrial aluminum foil, spraying it with Styrofoam, painting it, and then using the thing to disguise yourself as a rock and hide from the bad guys. But we’re guessing that, on average, system administrators have more need to display a File Open dialog box than they do to disguise themselves as rocks.

True. But we did say on average.