Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new 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.

Retrieving a Formatted List of Available Fonts

Kids these days have it so easy. When the Scripting Guys were young not only did we have to walk 20 miles through the snow just to get to school each day, but once we got there we had only one font available to us. And to make things even worse, that lone font was Courier!

Yes, we know: it’s hard to believe people were able to survive in such primitive conditions.

Today, of course, things are very different: today computers come prepackaged with scores of fonts, everything from Arial to Times New Roman to lovable old Wingdings. That’s great, but it does introduce a new set of difficulties: how can you tell which fonts are installed on a computer, and, equally important, how do you know what those fonts look like? (Because, let’s face it, most people don’t know their Estrangelo Edessa from their Eurostile™ Extended Two, at least not without looking.)

Of course, you can always use the venerable old Charmap.exe or the Fonts item in Control Panel to view the fonts available on a computer. Both these utilities work fine…provided you don’t mind looking at your fonts one at a time:

But what if you’d like a nice formatted list of all your fonts, a reference you can print out and, well, refer to from time-to-time? We know that neither Charmap.exe or Control Panel Fonts will help you there. But maybe there’s a way to use a script to create something like that…maybe – no, even a script probably couldn’t do that. Looks like we’re out of luck.

But wait. As it turns out – that’s right, you can write a script that leverages Microsoft Word to create such a reference sheet. How in the world did you know that?

Oh, right: the title of today’s column was kind of a dead giveaway, wasn’t it?

But that’s OK: now that the cat is out of the bag we can take a look at a script that retrieves a collection of the fonts installed on a computer and then, using each of those fonts, types the sentence The quick brown fox jumps over the lazy dog.

Here’s the script:

On Error Resume Next

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
objSelection.Font.Size = 14

For Each strFont in objWord.FontNames
    If strFont <> "Courier" Then
        objSelection.Font.Name = strFont
        objSelection.TypeText strFont & _
            ": The quick brown fox jumps over the lazy dog."
        objSelection.TypeParagraph
    End If
Next

We begin by creating an instance of Microsoft Word (Word.Applcation) and setting the Visible property to True; after all, there’s not much point in using all these fancy fonts if we can’t actually see them. We use the Add method to create a blank document, and then create an instance of the Word Selection object. Finally we set the Font.Size property of the Selection object to 14 point, and get ready for action.

It turns out that the Word object model includes a property – FontNames – that returns a collection of available fonts; all we have to do is create a For Each loop to cycle through that collection. Each time through the loop we grab a different font name – Arial, New York, Rockwell Condensed – and store the name in the variable strFont. We then use this line of code to change the current font to the font represented by strFont:

objSelection.Font.Name = strFont

With the font appropriately set we can then use these two lines of code to type the name of the font followed by our sample sentence (The quick brown fox jumps over the lazy dog.) followed by a carriage return-linefeed:

objSelection.TypeText strFont & _
    ": The quick brown fox jumps over the lazy dog."
objSelection.TypeParagraph

Got that? The TypeText method is used to type the specified text into our document while the TyepParagraph method is used to tack a carriage return-linefeed (equivalent to pressing ENTER on the keyboard) at the end of the sentence.

This process continues until we’ve printed out the target sentence in every available font. That will yield a Word document that looks something like this:

What a coincidence: “wow” was the only thing we could think of, too.

All in all this works pretty well: we now have a document that not only tells us the name of each font installed on a computer, but also shows us what each font looks like. We did encounter one oddity, however. You might note that in our For Each loop we check to see if the font name is Courier; if it is we do not print out our sample sentence but instead simply loop around and try the next font:

For Each strFont in objWord.FontNames
    If StrFont <> "Courier" Then
        objSelection.Font.Name = strFont
        objSelection.TypeText strFont & _
            ": The quick brown fox jumps over the lazy dog."
        objSelection.TypeParagraph
    End If
Next

Why? Well, to tell you the truth, we don’t know. When we left Courier in, however, the script would dutifully type out the sample sentence, in Courier, and then terminate. No error would be generated, but the script would just end. When we modified the script to prevent anything from being typed in Courier, then we got all the fonts, just as you’d expect. We aren’t sure if this a bug or a feature, and we aren’t sure if the problem lies with Word or with our test computer; that’s something we’ll have to look into. (Although the problem – which crops up on Windows XP Service Pack 1 -- seems to have been solved in Windows XP Service Pack 2.) If you’re desperate to know what Courier looks like, however, change your For Each loop to this and see if it works for you:

For Each strFont in objWord.FontNames
    objSelection.Font.Name = strFont
    objSelection.TypeText strFont & _
        ": The quick brown fox jumps over the lazy dog."
    objSelection.TypeParagraph
Next

Like we said, we old-timers will look into this, right after we finish reminiscing about the days when MTV actually played music videos. (No, really, we swear, we are not making that up.)