Hey, Scripting Guy! How Can I Tally All the Items in a Microsoft Word Document?

ScriptingGuy1

Hey, Scripting Guy! We have an application that repeatedly runs a test and writes the result of each test pass to a Microsoft Word document. Periodically I’d like to be able to use a script to tally up the number of times a test passed, the number of times a test failed, and the number of times a test failed to run. How can I write a script like that?

— GK

Hey, GK. Before we begin we’d like to take a moment to address the Scripting Son. Hey, Scripting Son: ha!

If you’re wondering what that’s all about (a surprisingly common occurrence for readers of this column) every now and then the Scripting Son will tell the Scripting Dad a story about one of his friends. “You know, you have some weird friends,” the Scripting Dad will say. “Well, at least I have friends,” the Scripting Son will shoot back. To which the Scripting Dad will say – well, not much. After all, when you’re a career-driven workaholic like the Scripting Guy who writes this column, well, who has time to make friends?

Now, however, the Scripting Guy who writes this column has the perfect rejoinder to the Scripting Son: ha!

As of this writing, the Scripting Guys group on Facebook has 517 members. No friends? How about 517 friends, Scripting Son? That’s way more members than the interestingly-named I Went School At Brokington College, which only has 70 members. And while we still lag behind Keith Olbermann is Our Edward R. Murrow (646 members), well, better keep looking over your shoulder, Keith. The Scripting Guys are gaining on you.

So why do the Scripting Guys have so many friends? Well, we’d like to think it’s because of our charming personalities. More likely, however, it’s because we have scripts that can tally the items found in a Microsoft Word document:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Open("C:\Scripts\Test.doc")
Set colParagraphs = objDoc.Paragraphs

For Each objParagraph in colParagraphs
    strText = objParagraph.Range.Text
    intLength = Len(strText)
    strText = Left(strText, intLength - 1)
    Select Case strText 
        Case "Passed" intPassed = intPassed + 1
        Case "Failed" intFailed = intFailed + 1
        Case "Not run" intNotRun = intNotRun + 1
    End Select
Next

Wscript.Echo "Passed: " & intPassed
Wscript.Echo "Failed: " & intFailed
Wscript.Echo "Not run: " & intNotRun

As you can see, we kick things off by creating an instance of the Word.Application object and then setting the Visible property to True; that gives us a running instance of Microsoft Word that we can see onscreen.

objWord.Quit

Once we have an instance of Word up and running we use the Open method to open the document C:\Scripts\Test.doc. We then use this line of code to return a collection of all the paragraphs in that document:

Set colParagraphs = objDoc.Paragraphs

Why do we want a collection of all the paragraphs in the document? Well, as GK noted in her email, each “paragraph” in the document is simply a line indicating whether a test pass succeeded, did not succeed, or failed to run. In other words, it looks something like this:

Passed
Failed
Failed
Passed
Failed
Not run

By grabbing all the paragraphs and looking at the text of each one, we can easily count the number of times a test passed, the number of times it failed, and the number of times it did not run. Which is just exactly what we’re going to do next.

With our collection of paragraphs safely in hand, our next step is to set up a For Each loop to loop through each item in this collection:

For Each objParagraph in colParagraphs

The first thing we do inside this loop is grab the value of the Range.Text property; as you might have guessed, this returns the text for each paragraph:

strText = objParagraph.Range.Text

We then encounter these two lines of code:

intLength = Len(strText)
strText = Left(strText, intLength - 1)

What are these two lines for? Well, the “text” for each paragraph also includes an end-of-paragraph mark that makes it harder for us to pinpoint the actual text. And what do you do if something causes you a problem? That’s right: you get rid of it altogether. (Editor’s Note: This doesn’t always work, which should be obvious by the fact that there still is a Scripting Editor. Ha!) That’s exactly what we’re doing here. In the first line, we use the Len function to determine the length (number of characters) in the string strText. In the second line, we use the Left function to grab everything except the last character (the length of the string minus 1). For example, suppose strText equals this, with the asterisk representing that troublesome last character:

Passed*

Here’s what strText will be equal to after we execute those two lines of code:

Passed

Much better.

The rest is easy. Our next step is to set up a Select Case block based on the value of strText:

Select Case strText 
    Case "Passed" intPassed = intPassed + 1
    Case "Failed" intFailed = intFailed + 1
    Case "Not run" intNotRun = intNotRun + 1
End Select

As you can see, if strText is equal to Passed we increment a counter variable named intPassed by 1. Because intPassed has never been used or declared, that means it starts life with a value of 0; that also means that, the first time we encounter the word Passed, we add 1 to the variable, making it equal to 1. If strText is equal to Failed we increment a variable named intFailed, and if strText is equal to Not run we increment a variable named intNotRun. Could it be any easier?

All that’s left at that point is to echo back the results of our tally:

Wscript.Echo "Passed: " & intPassed
Wscript.Echo "Failed: " & intFailed
Wscript.Echo "Not run: " & intNotRun

That final report will look something like this:

Passed: 12
Failed: 9
Not run: 6

That’s all there is to it.

By the way, in case you’re wondering, the Scripting Son has 100 friends on his MySpace page. By now you should know what we have to say about that.

ScriptingGuy1

Follow