How Can I Retrieve Field Values in a Microsoft Word Document?
Hey, Scripting Guy! I have a bunch of Word documents that include fields like LastSavedBy. How can I write a script that retrieves the current values for these fields?
Hey, OS. You know, the other night the Scripting Guy who writes this column was watching TV when he saw an ad for a new Monopoly game: Monopoly, the Electronic Banking Edition. What was interesting (or disturbing, depending on your point of view) is that this version of Monopoly does away with cash; instead, players use debit cards and an ATM-like machine to keep track of their winnings.
In case you’re wondering, the Scripting Guy who writes this column found this disturbing. After all, there are few things in life more satisfying than watching one of your brothers grimly counting out every last Monopoly dollar he has, desperately hoping to reach the $1000 in rent he owes you. (And all the while you saying, “Tell you what, you give me your two yellow properties and you won’t owe me a thing.”) Having him simply swipe his debit card doesn’t sound like much fun at all. Sitting there with a huge pile of cash in front of you is absolutely the greatest feeling in the world.
Just ask – uh, never mind. There are certain people even the Scripting Guys shouldn’t make jokes about.
Sign of the times. Technically, the game doesn’t use just any old debit card: it’s a Visa debit card. Also, the playing pieces have been updated for the Banking Edition; these pieces now include: a Segway personal transporter (which is nice; after all, most of us will never see a Segway in real life); a space shuttle; a flat-screen TV; a baseball cap; a dog in handbag (yes, a handbag); and – the piece everyone will likely fight over – a tin of Altoids.
Anyway, the Scripting Guy who writes this column has no intention of playing the Electronic Banking Edition; he much prefers the old-fashioned version. And he will never, ever be the tin of Altoids. Instead, he prefers to be the race car any time he plays Monopoly. But, then again, who doesn’t?
Note. Sigh. As you might expect, the Scripting Editor doesn’t like being the race car; she prefers to be the shoe. (You heard right: the shoe.) We assume that’s only because the game doesn’t have any playing pieces even more boring than the shoe.
But it doesn’t really matter; after all, who has time for games anyway? Not us, not when we need to write a script that can retrieve field values from a Microsoft Word document:
Set objWord = CreateObject(“Word.Application”) objWord.Visible = True
Set objDoc = objWord.Documents.Open(“C:\Scripts\Test.doc”)
Set colFields = objDoc.Fields colFields.Update
For Each objField in colFields Select Case objField.Type Case 20 Set objRange = objField.Result strUser = objRange.Text Wscript.Echo “Last Saved By: ” & strUser End Select Next
Let’s meander through the code and see if we can figure out how this all works. To begin with, 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. We then use this line of code to open the file C:\Scripts\Test.doc:
Set objDoc = objWord.Documents.Open(“C:\Scripts\Test.doc”)
Well, that was easy enough, wasn’t it? Like taking candy from a baby.
Or playing Monopoly with your brothers.
After we open the document we then use the following line of code to retrieve a collection of all the fields in that document:
Set colFields = objDoc.Fields
Once we have the collection in hand we call the Update method to update each of these fields. By calling the Update method we get the latest, most up-to-the-minute value for each field in the document; we’re assuming that this is what most of you will want to do. On the other hand, some of you might want to retrieve the value of the fields at the time the document was last saved. That’s fine; just comment out (or remove) the following line of code and your script will not update all the fields when it opens the document. The commented code would end up looking like this:
Our next task involves setting up a For Each loop to loop through the collection of fields. As it turns out, Microsoft Word identifies fields by their Type, the Type property being a unique integer value. For example, the LastSavedBy field has a value of 20; the Author field has a value of 17. In our script, we set up a Select Case statement to examine the value of the Type property and then echo back the appropriate field name. As you can see, our Select Case statement only looks for one particular field type: the LastSavedBy field, which has a value of 20. However, you can easily extend the Select Case statement to account for other field types. For example, add this line of code to check for – and echo back the value of – the Author field:
Select Case objField.Type Case 17 Set objRange = objField.Result strUser = objRange.Text Wscript.Echo “Author: ” & strUser
Or, just use this code to echo back all the values, without specifying the field type:
For Each objField in colFields Set objRange = objField.Result Wscript.Echo objRange.Text Next
We’ll leave that up to you. If you don’t need to echo back the field Type, or if you, say, want to be the shoe, well, hey, to each his own.
Note. Good point: how are you supposed to know that the Author field has a Type equal to 17? Well, probably the best way is to go to the Microsoft Word VBA Language Reference on MSDN and take a peek at the wdFieldType enumeration.
Let’s assume that we do find the Last Saved By field (a field that has a Type equal to 20). In that case, we execute this block of code:
Set objRange = objField.Result strUser = objRange.Text Wscript.Echo “Last Saved By: ” & strUser
This is a little goofy, but in order to echo the value of the field we first need to create a Range object encompassing the field’s Result (that is, its value). We use this line of code to create the Range object:
Set objRange = objField.Result
Once we have a Range object we can then assign the value of that object’s Text property to a variable named strUser. And then we can simply echo back the message “Last Saved By: “ along with the name of the person who last saved the document:
Wscript.Echo ” Last Saved By: ” & strUser
And that, as they say, is that.
As you might expect, when he saw the commercial for Monopoly: The Electronic Banking Edition the Scripting Guy who writes this column immediately thought, “What are they trying to do, ruin the game completely?” Interestingly enough, after doing a little research he discovered that this isn’t the first time Parker Brothers has tried to ruin the game completely. In 1934 Charles Darrow attempted to sell his new creation to the company; Parker Brothers turned him down, claiming that the game took too long to play and was way too complicated. Darrow thus set out on his own, and the game began selling like hotcakes. Needless to say, Parker Brothers reconsidered and, a year later, bought the rights.
Monopoly quickly became the best-selling game Parker Brothers had ever had; in fact, its sales were credited with saving the company from bankruptcy. So what did Parker Brothers do? In 1936 they decided to stop making the game, assuming that it was just a fad that would soon die out. A sharp spike in sales caused them to change their mind, and from that day on– well, you know the rest of the story.
Note. Believe it or not, the Scripting Guy who writes this column didn’t know the history of Monopoly right off the top of his head; as we noted, he had to go out and research this. And yes, that is one of the perks about being a Scripting Guy: you can waste time reading about the history of Monopoly and get paid for doing so! After all, it’s research, right?