Learn about Windows PowerShell
Hey, Scripting Guy! I have a bunch of reports and log files that are saved as Microsoft Word documents. I’d like to be able to search for a specific word in one of these documents (e.g., the word “Failed”) and then change the background color of any paragraph that the target word appears in. How can I do that?-- XD
Hey, XD. You know, a lot of people have written in over the course of the 2008 Winter Scripting Games to say things like this: “I bet you guys have a really cool automated way of testing scripts. Any chance you could share that procedure with us?” Well, to tell you the truth, we could share that procedure with you, except for one thing: we don’t actually have such a procedure.
Not that we wouldn’t like to have one, mind you, and not that we don’t have an idea as to how an automated script tester might work. However, there are a couple of problems that make automated testing, at least for the moment, impossible. For one thing (and for reasons we won’t get into) we aren’t allowed to have an online submission form for the Games; instead, all the entries are sent via plain old email. That makes automated testing really hard. Why? Well, to begin with, people don’t always format their emails correctly. Some people send in entries that look like this:
While others send in entries that look like this:
It’s easy for us to figure out which is the name and which is the country, but not so easy for the computer.
Needless to say, Scripting Guys are way smarter than computers.
We also run into major line break issues with email. For example, you might have a script that looks like this:
For x = 1 to 100 Wscript.Echo xNext
Unfortunately, by the time it gets emailed the script might look like this:
For x = 1 to 100 Wscript.Echo x Next
Again, the Scripting Guys can – usually – figure out where the line breaks belong. (And yes, we dutifully put in all the missing line breaks before we test a script.) An automated tester would simply fail this script.
Hmmm, spend 10 minutes trying to figure out where line breaks go in a script, or spend 1 second marking a script as having failed. Maybe the Scripting Guys aren’t smarter than computers after all ….
Oh, and then there are the signatures that get automatically attached to an email and – well, you get the idea. An online submission form would help eliminate most of the problems. Without one ….
The other problem with fully-automated testing is that the Scripting Guys are soft-hearted; computers are not. Sometimes we’ll get a script that looks like this:
For x = 1 to 100 Wscript.Echo xNetx
It’s obvious what happened here; the script writer simply mistyped the word Netx. A computer would mark this script as having failed. For a simple problem like this (or a path that says C:\Script\Test.txt when it’s supposed to say C:\Scripts\Test.txt), well, the Scripting Guys will usually fix the problem and then try the script again.
We even give people a pass if we say “Display the answer using two decimal places” and they instead display the answer using four decimal places. Or if they display the answer in a message box when we said, “Please write the answer to the command window.” Etc. etc.
But don’t tell anyone. After all, we have a reputation for being ruthless and cold-blooded, and we’d hate for people to know the truth.
Anyway, if you’re one of those people wondering about the Scripting Guys and their automated testing system, well, there’s your answer. And if you’re one of those people wondering about how you can change the background color of a paragraph if a specified word appears somewhere in that paragraph, well, here’s your answer:
Const wdTexture10Percent = 100
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 If InStr(strText, "Failed") Then objParagraph.Shading.Texture = wdTexture10Percent End IfNext
No doubt some of you are looking at this script and thinking, “Wait a second. That’s not going to work; they’re not even using Word’s Find object to find the target text.” That’s true; we’re not using Word’s Find object to find the target text. Why not? Well, the Find object is quite good at finding text, and it’s pretty darn good at letting you modify the found text; what it isn’t good at is letting you work with the surrounding text. Is that a problem? You bet it is. The Find object would make it easy for us to change the background color of the target text itself (that is, the word Failed); however, it would not make it easy for us to change the background color of the entire paragraph where that word appears. That’s the problem.
Because of that we decided to take another approach. In this script, we return a collection of all the paragraphs in the document, then search each paragraph, one-by-one, to see if it contains the word Failed. If it does, well, at that point it’s easy to change the background color of the paragraph; after all, we’re already working with that paragraph. And if it doesn’t contain the target word, hey, no big deal; we simply go on and check the next paragraph in the collection.
To get all that to work we start by defining a constant named wdTexture10Percent and setting the value to 100; that’s going to allow us to set the background color (technically, the Shading.Texture property) to 10% grey. After defining the constant, we create an instance of the Word.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Word that we can see onscreen. After that, we use the following line of code to open the file C:\Scripts\Test.doc:
With the document open we can retrieve a collection of all the paragraphs in that document simply by creating an object reference to the Paragraphs property:
At this point, we’re ready to have to some real fun.
Oh, never mind; we forgot that we have to finish this column. And then test about 3,000 more Scripting Games submissions. But then we’ll be ready to have some real fun.
In order to complete the task (and finish this column) we set up a For Each loop that enables us to walk through all the paragraphs in the document. Inside that loop, we grab the value of the paragraph’s Range.Text property and store it in a variable named strText:
strText = objParagraph.Range.Text
As you probably guessed, the Text property stores all the text that appears in the paragraph. With the text safely tucked into the variable strText we can then use VBScript’s InStr method to see if the target word Failed appears anywhere in that text:
If InStr(strText, "Failed") Then
If it doesn’t, well, like we said, no big deal: we simply loop around and repeat the process with the next paragraph in the collection. If the target word does appear in the paragraph then we use this line of code to set the paragraph’s background color to 10% grey:
objParagraph.Shading.Texture = wdTexture10Percent
And then it’s back to the top of the loop, where we try again with the next paragraph in the document. When all is said and done, we should have a document that looks something like this:
That should do it, XD. Give it a try and see for yourself.
So how do the Scripting Guys test scripts for the Scripting Games? Well, believe it or not, the process works like this: We get an email from someone, then we copy the code from that email and paste it into a script file. We then run the script and see what happens. If the script fails with an error message we check to see if the problem was due to a line break issue or a simple typo. We then try again. This process continues until the script succeeds or until we decide that the script has failed. (Usually we keep trying until we get the script to run without an error. At that point, if the script fails to carry out the appointed task, we give the entry a 0.)
And then we repeat the process with the next script. Thus far we’ve tested over 3,000 scripts in this manner, with another 3,000 sitting in the Inbox waiting their turn.
And yes, as a matter of fact that is every bit as fun as it sounds. But anything for the Scripting Games, right?
Although, we must admit, we won’t be entirely heartbroken when the Games are over.
Note. Speaking of which, the Games are far from over. (Oh, how … nice ….) The Games run through Monday, March 3rd, which means you have plenty of time to submit entries. And, not-so-incidentally, win some fantastic prizes.