Hey, Scripting Guy! How Can I Convert to Windows PowerShell a VBScript Script That Creates a Table in Office Word?

Hey, Scripting Guy! How Can I Convert to Windows PowerShell a VBScript Script That Creates a Table in Office Word?

  • Comments 2
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! I was looking through the Office Space Archive and came across a nice article that talks about how to create a table in Office Word. The article is interesting, but I am more interested in how to do this by using Windows PowerShell. Will I be able to translate this script to Windows PowerShell? Or do you have one already written that uses Windows PowerShell to do this?

- DC

SpacerHey, Scripting Guy! Answer

Hi DC,

You know, the great thing about Tech·Ed is the opportunity to meet and talk with people from around the world. We have enjoyed renewing our friendships with people we met years ago and making new friends. It is refreshing to be able to talk to so many people who share common interests. The good thing about having Tech·Ed in Los Angeles is there are world-class restaurants—and don’t forget walking on perpetual sunshine. Because we are about to go into brain overload from all the excellent sessions, we will go ahead and answer your question. We do not have a Windows PowerShell script that creates tables in an Office Word document. So you will have to bear with us for a minute while we translate the one that you referred to on the Office Space page. Speaking of bear with us, here is a Bornean sun bear Ed saw at the San Diego Zoo who may have attended one Tech·Ed session too many.

Image of a Bornean sun bear

 

This week we are looking at how to migrate VBScript to Windows PowerShell. You should definitely check out the VBScript-to-Windows PowerShell Conversion Guide. This is included as Appendix C in the Microsoft Press book, Microsoft Windows PowerShell Step by Step. It is also in the Windows PowerShell Graphical Help File. Clearly, we are proud of that thing. You may also want to check out our Windows PowerShell Scripting Hub where you will find links to the Windows PowerShell Owner's Manual (very popular!) and other resources that will help you to convert VBScript to Windows PowerShell. One additional book that would be useful is the Microsoft Press book, Windows PowerShell Scripting Guide. This book is useful if you are working with WMI, or if you are trying to go beyond simple line-by-line translations of one script to another.

The GetServiceCreateWordTable.vbs from the Office Space Creating Tables in Microsoft Word article is shown here for ease of comparison:

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)

The GetServiceCreateWordTable.ps1 Windows PowerShell version is an exact translation of the GetServiceCreateWordTable.vbs script. The first thing that you have to do is to create two variables. The first variable is used to determine how many rows will be initially created when the table is created. The second variable determines the number of columns. In the GetServiceCreateWordTable.ps1 script, the two variables that were created are constants. In Windows PowerShell if you want to create a constant, you use the New-Variable cmdlet as shown here:

PS C:\> New-Variable -Name Number_Of_Rows -Value 1 -Option constant

If you try to change the value of the $Number_Of_Rows constant, you will receive an error message as shown here:

PS C:\> $Number_Of_Rows = 5
Cannot overwrite variable Number_Of_Rows because it is read-only or constant.
At line:1 char:16
+ $Number_Of_Rows  <<<< = 5

In our "exact translation," this is the only thing we did not exactly translate. There is no reason to make the variables constant because we are not changing their values. This is seen here:

$Number_Of_Rows = 1
$Number_Of_Columns = 3

Every script that works with Word Automation must create an instance of the Word.Application object. In Windows PowerShell, you use the New-Object cmdlet specify the comobject parameter and supply the name of the Program ID. This is shown here:

$objWord = New-Object -comobject word.application

After the Word.Application object is created, the GetServiceCreateWordTable.vbs script sets the visible property to $true, adds a document, and creates a new range object. For more information about this, look at Monday’s Hey, Scripting Guy article. This section of code is seen here:

$objWord.Visible = $true
$objDoc = $objWord.Documents.Add()
$objRange = $objDoc.Range()

The next step is to add a table. To do this, you use the Add method from the Tables collection. The Add method is shown in Table 1. The Add method has three required parameters: Range, NumRows, and NumColumns. The GetServiceCreateWordTable.vbs script supplies these three parameters, but does not use any of the optional parameters. Each value for the parameters is held within variables. This makes reading the method call and changing the method call easy. This is seen here:

$objDoc.Tables.Add($objRange,$Number_Of_Rows,$Number_Of_Columns)

Table 1 The Add method
Name Required/Optional Data Type Description

Range

Required

Range object

The range where you want the table to appear. The table replaces the range, if the range is not collapsed.

NumRows

Required

Long

The number of rows that you want to include in the table.

NumColumns

Required

Long

The number of columns that you want to include in the table.

DefaultTableBehavior

Optional

Variant

Sets a value that specifies whether Microsoft Word automatically resizes cells in tables to fit the cells’ contents (AutoFit). Can be either of the following constants: wdWord8TableBehavior (AutoFit disabled) or wdWord9TableBehavior (AutoFit enabled). The default constant is wdWord8TableBehavior.

AutoFitBehavior

Optional

Variant

Sets the AutoFit rules for how Word sizes tables. Can be one of the WdAutoFitBehavior constants.

The GetServiceCreateWordTable.vbs script next retrieves the first table from the Tables collection. The VBScript line of code is seen here:

Set objTable = objDoc.Tables(1)

In Windows PowerShell this is the code to retrieve the first table from the Tables collection:

$objTable = $objDoc.Tables.item(1)

VBScript requires you to use the Set statement when a method returns an object. The confusing section of the script is the VBScript code that calls the Item method from the Tables collection. You might not know this because the Item method is the default method from the Tables collection, and in VBScript it is not required to be mentioned. The .NET Framework does not allow you to omit method names, and this is the reason why you use it in the Windows PowerShell code. It is a bad practice to leave off method names in VBScript because most readers will not realize that it is missing, and they will probably assume that they are using a Tables method or even querying a Tables property from a document object. When you run the GetServiceCreateWordTable.ps1 script, it probably is not important if you know exactly what the script is doing. But when you have to change or troubleshoot the script, you must know exactly what the script is doing.

The table column headings are produced by using the Cell method from the Table object. The Cell method returns a Cell object. The range property from the cell object returns a Range object. The range object has a Text property that enables you to set or retrieve text from the Range object. This is shown here:

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

The GetServiceCreateWordTable.vbs script assigns the number 2 to the X variable. This refers to the second row in the table and is the starting point for writing the WMI data to the newly created Office Word table. Then it performs the WMI query. This section of the script is shown here:

x = 2

strComputer = "."

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

In the GetServiceCreateWordTable.ps1 script, this section of the script is simplified because of the use of the Get-WmiObject cmdlet. This is seen here:

$x = 2
$strComputer = "."

Get-WmiObject -query "Select * from Win32_Service" -computername $strComputer

Because we are doing a script translation, we decided to use the legacy WQL syntax with the Get-WmiObject. This compatability mode of using Windows PowerShell is discussed in How do I migrate my VBScript WMI queries to Windows Powershell. If we were writing this script from scratch, we would query the Win32_Service class directly:

Get-WmiObject –class Win32_Service –computername $strComputer

The GetServiceCreateWordTable.vbs script stores the WMI service information in the colItems variable and uses the For…Each…Next statement to walk through the collection. Inside the loop it adds a row to the table, retrieves the WMI property values, and writes the information to the Text property of the Range object in the current cell range. When it is finished writing to the current cells, it increments the value of the x variable and continues to the next objItem in the collection. This is shown here:

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

The GetServiceCreateWordTable.ps1 script does not use the ForEach statement to walk through a collection of WMI objects. Instead, it uses the Windows PowerShell pipeline and streams the WMI data as it is received from WMI across the pipeline to the ForEach-Object cmdlet. Inside the script block of the ForEach-Object cmdlet the script adds a new row, writes to the text property of the Range object of the Cell object, and uses the $_ automatic variable to refer to the current WMI object that is located on the pipeline. By not storing and forwarding the WMI data, the script is more efficient. We increment the value of the $x variable by using the ++ operator. This section of code is shown here:

Foreach-Object {
 $objTable.Rows.Add()
 $objTable.Cell($x,1).Range.Text = $_.name
 $objTable.Cell($x,2).Range.Text=$_.DisplayName
 $objTable.Cell($x,3).Range.Text=$_.State
 $x++
} #end Foreach-Object

The last thing that must be done is to format that Word table. The GetServiceCreateWordTable.ps1 script provides an enumeration value directly to the AutoFormat method from the Table object. The Format parameter will accept either a WdTableFormat enumeration value or a WdTableFormatApply enumeration value. The number 9 corresponds to the wdTableFormatColorful2 format. Colorful is a good name for table format number 9. You can see it for yourself here:

Image of a table created in Office Word

 

The parameters of the AutoFormat method from the Table object are discussed in Table 2.

Table 2 The parameters of the AutoFormat method from the Table object
Name Required/Optional Data Type Description

Format

Optional

Variant

The format to apply. This parameter can be a WdTableFormat constant, a WdTableFormatApply constant, or a TableStyle object.

ApplyBorder

Optional

Variant

True to apply the border properties of the specified format. The default value is True.

ApplyShading

Optional

Variant

True to apply the shading properties of the specified format. The default value is True.

ApplyFont

Optional

Variant

True to apply the font properties of the specified format. The default value is True.

ApplyColor

Optional

Variant

True to apply the color properties of the specified format. The default value is True.

ApplyHeadingRows

Optional

Variant

True to apply the heading-row properties of the specified format. The default value is True.

ApplyLastRow

Optional

Variant

True to apply the last-row properties of the specified format. The default value is False.

ApplyFirstColumn

Optional

Variant

True to apply the first-column properties of the specified format. The default value is True.

ApplyLastColumn

Optional

Variant

True to apply the last-column properties of the specified format. The default value is False.

AutoFit

Optional

Variant

True to decrease the width of the table columns as much as possible without changing the way text wraps in the cells. The default value is True.

The GetServiceCreateWordTable.ps1 script is shown here:

$Number_Of_Rows = 1
$Number_Of_Columns = 3

$objWord = New-Object -comobject word.application
$objWord.Visible = $true
$objDoc = $objWord.Documents.Add()
$objRange = $objDoc.Range()
$objDoc.Tables.Add($objRange,$Number_Of_Rows,$Number_Of_Columns)
$objTable = $objDoc.Tables.item(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 = "."

Get-WmiObject -query "Select * from Win32_Service" -computername $strComputer |
Foreach-Object {
 $objTable.Rows.Add()
 $objTable.Cell($x,1).Range.Text = $_.name
 $objTable.Cell($x,2).Range.Text=$_.DisplayName
 $objTable.Cell($x,3).Range.Text=$_.State
 $x++
} #end Foreach-Object

$objTable.AutoFormat(9)

DC, that is about all we have time for today. Hope you have enjoyed all of the words that Ed wrote and Craig produced, edited, published, and quality checked. (Ed was over his word count by 33 percent!) Take care, and join us tomorrow.

Ed Wilson and Craig Liebendorfer, Scripting Guys

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hey Scripting Guys

    Do you know a resource wher I can find how to convert VBA to Vbscript

  • Hey Scripting Guys

    Do you know a resource where I can find how to convert VBA to Vbscript