Office Space: Tips and Tricks for Scripting Microsoft Office Applications

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

Creating Tables in Microsoft Word

When Ted Williams, the last major leaguer to bat better than .400 for a season, was asked for his philosophy of hitting, he famously replied, “See the ball, hit the ball.” There you have it: simple, straightforward, and to the point. Want to be a Hall of Famer like Ted Williams? Then just see the ball and hit the ball.

Of course, the one problem with Ted’s advice is that – as the Scripting Guys can attest from painful experience – there’s a big gap between seeing the ball and being able to hit the ball. “See the ball, hit the ball” sounds pretty good, but it appears as though there are a few steps missing. Knees bent or not bent? Hands held high or hands held low? Open stance or closed stance? Arms extended or arms not extended? Ultimately, you have to wonder just how useful that advice really is.

Would the Scripting Guys ever dispense advice that sounded good but was of little practical value? Well, unfortunately, the answer is yes. As script writers ourselves we’re well aware of the limitations of the command window, at least when it comes to displaying output in tabular format. Granted, you can do a little fancy string manipulation to make your output line up in nice, neat columns, but that’s about it: you can’t display that output using different colors or different fonts, you can’t include pictures, you can’t shade alternate rows of the table, you can’t do much of anything. And how do the Scripting Guys recommend you deal with this problem? “Create your tables in Microsoft Word instead.”

Excellent advice, except for one thing: isn’t there a big gap between saying you should create tables in Microsoft Word and actually creating those tables in Microsoft Word? Isn’t this pretty much the same thing as telling aspiring baseball players to see the ball, hit the ball?

Well maybe, but then again, maybe not. As it turns out, it’s pretty easy to programmatically create tables in Microsoft Word. So easy, in fact, that in today’s column we’ll show you how to create a basic table and how to create a table on-the-fly; as a bonus, we’ll even show you a single line of code that can autoformat your tables. Sure, Ted Williams won the Triple Crown – twice – but we bet he never once wrote a VBScript script that created a table in Microsoft Word.

So how easy is it to create a table in Microsoft Word? This easy:

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 3

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

Set objRange = objDoc.Range()

objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

Set objTable = objDoc.Tables(1)

objTable.Cell(1, 1).Range.Text = "Row 1, Column 1"
objTable.Cell(1, 2).Range.Text = "Row 1, Column 2"
objTable.Cell(1, 3).Range.Text = "Row 1, Column 3"

This script starts off by defining a pair of constants: NUMBER_OF_ROWS, which represents the number of rows in our table, and NUMBER_OF_COLUMNS, which represents the number of columns. (Note that you don’t need to know the number of rows in advance; we’ll show you how to dynamically add rows to a table.) We then create an instance of the Word.Application object, set the Visible property to True (just so we can watch the magic unfold), and then use the Add method to create a new document. With our new document in hand, we then use this line of code to create an instance of the Range object, which we’ll need in order to create our table:

Set objRange = objDoc.Range()

We’re now ready to add a table to the document. We do this by adding a new table to the Tables collection; that can be done by calling the Add method and passing it three parameters:

• objRange. An object reference to the Range object we created. This represents the document location where we want to put the table.

• NUMBER_OF_ROWS. The number of rows in the table. In this first example, we’re creating a table with just one row.

• NUMBER_OF_COLUMNS. The number of columns in the table. In this example, we’re creating a table with three columns.

The code itself looks like this:

objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

That’s all we have to do; the script will insert a 1-row, 3-column table, and our document will look something like this (we’ve added the gridlines just so you can see that the table really does exist):

OK, we’ll buy that argument: this table would be a little more impressive if it actually contained some data. But that’s what the last few lines of code in our script do. To add data to a table we first have to create an object reference to the table; that’s what we do here:

Set objTable = objDoc.Tables(1)

That line of code simply says that the variable objTable will refer to the first item in the Tables collection; that’s what objDoc.Tables(1) represents. What if we had multiple tables in our document and we wanted to work with table number 3? Then we’d use code that looked like this:

Set objTable = objDoc.Tables(3)

After we create our object reference we can then type text into the table cells using code similar to this:

objTable.Cell(1, 1).Range.Text = "Row 1, Column 1"

As you can see, we’re referencing cell 1,1 (row 1, column 1) in our table. We’re grabbing the Range object for the cell (each cell has its own Range object) and then setting the value of the Text property to “Row 1, Column 1.” We then repeat this process for the other two cells in row 1. When the script finishes executing, our table will look like this:

Which would you rather have: 521 lifetime home runs, like Ted Williams, or a table that looks like this? That’s what we thought.

Of course, you might be thinking, “OK, that was pretty easy…as long as I knew, in advance, that my table would have only a single row. But what if I’m grabbing information from the event logs or someplace and I don’t know how many rows I need? What am I supposed to do then?”

We’ll tell you: you’re supposed to use the Rows.Add() method any time you need to add a new row to a table. Here’s a modified script that creates a table, then adds three blank rows onto the end:

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 3

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

Set objRange = objDoc.Range()

objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

Set objTable = objDoc.Tables(1)

objTable.Cell(1, 1).Range.Text = "Row 1, Column 1"
objTable.Cell(1, 2).Range.Text = "Row 1, Column 2"
objTable.Cell(1, 3).Range.Text = "Row 1, Column 3"

objTable.Rows.Add()
objTable.Rows.Add()
objTable.Rows.Add()

objTable.AutoFormat(16)

Oh, we also added this line of code to the end of the script:

objTable.AutoFormat(16)

Why did we do that? Well, by default our script creates a table with no formatting whatsoever: no color, no shading, no gridlines. By calling the AutoFormat method and passing it the value 16 (indicating that we want to use the Table Grid format), we can quickly and easily format our table.

Here’s what our more dynamic and auto-formatted table looks like:

Much better.

Before we call it a day let’s take a look at a more practical example. Here’s a script that retrieves information about the services installed on a computer and then displays that information in a formatted Word table:

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 3

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

Set objRange = objDoc.Range()
objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS
Set objTable = objDoc.Tables(1)

objTable.Cell(1, 1).Range.Text = "Service Name"
objTable.Cell(1, 2).Range.Text = "Display Name"
objTable.Cell(1, 3).Range.Text = "Service State"

x = 2

strComputer = "."

Set objWMIService = _
    GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objItem in colItems
    objTable.Rows.Add()
    objTable.Cell(x, 1).Range.Text = objItem.Name
    objTable.Cell(x, 2).Range.Text = objItem.DisplayName
    objTable.Cell(x, 3).Range.Text = objItem.State
    x = x + 1
Next

objTable.AutoFormat(9)

As you can see, we begin by creating yet another single-row, three-column table. Because we want row 1 to serve as the header row, we use the following lines of code to add some text to the three cells in row 1:

objTable.Cell(1, 1).Range.Text = "Service Name"
objTable.Cell(1, 2).Range.Text = "Display Name"
objTable.Cell(1, 3).Range.Text = "Service State"

Next we assign the value 2 to a variable named x. Why 2? Well, we’re going to use x to keep track of the current row in the table. When we start to add data to the table, we want to start in row 2; after all, we’ve already used row 1 as our header row. Thus x = 2.

After that we use some standard WMI code to return a collection consisting of all the services installed on a computer. When we walk through the collection (inside our For Each loop) we first call the Rows.Add() method to add a new row to the table; the first time through the loop, this will be row 2. (Remember, row 1 already exists and is acting as our header row.) We then use these lines of code to add the service Name, DisplayName, and State to the three cells in that row:

objTable.Cell(x, 1).Range.Text = objItem.Name
objTable.Cell(x, 2).Range.Text = objItem.DisplayName
objTable.Cell(x, 3).Range.Text = objItem.State

Notice that we don’t hard-code in the row number; instead we use the variable x. After filling in the three cells in the row we use this line of code to increment the value of x:

x = x + 1

x is now equal to 3, which means that the next time through the loop we’ll be writing data to the cells in row 3. See how that works?

At the end of the script we use the AutoFormat method to apply the wdTableFormatColorful2 format and we’re done:

It’s like we Scripting Guys always said: See the table, script the table.