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?
Hey, GK. Before we begin we’d like to take a moment to address the Scripting Son. Hey, Scripting Son: ha!
Note. What’s that? Really? Wow. We always assumed that any time parents had something to tell their children they did it through a daily scripting column. Guess you learn something new every day, huh?
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.
Note. How are the Scripting Guys doing in their Facebook battle against MSDN and TechNet? We’re so glad you asked that question. At the moment, MSDN has 99 members and TechNet has 56. Hey, MSDN and TechNet: ha!
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.
Note. In our sample scripts we typically make Word visible simply so you know for sure that something is happening. However, this script works just fine even if Word is never visible on screen. To keep Word from appearing, comment out the line of code that sets the Visible property to True, and then add this line of code to the end of the script:
This ensures that Word properly terminates after the script finishes doing its thing.
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:
Here’s what strText will be equal to after we execute those two lines of code:
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.
Note. You say that you’d like to jump on the bandwagon, too? We’d love to have you; click here for details on joining the Scripting Guys Facebook group.