{"id":53763,"date":"2009-05-13T23:22:00","date_gmt":"2009-05-13T23:22:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/05\/13\/hey-scripting-guy-how-can-i-convert-to-windows-powershell-a-vbscript-script-that-creates-a-table-in-office-word\/"},"modified":"2009-05-13T23:22:00","modified_gmt":"2009-05-13T23:22:00","slug":"hey-scripting-guy-how-can-i-convert-to-windows-powershell-a-vbscript-script-that-creates-a-table-in-office-word","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-convert-to-windows-powershell-a-vbscript-script-that-creates-a-table-in-office-word\/","title":{"rendered":"Hey, Scripting Guy! How Can I Convert to Windows PowerShell a VBScript Script That Creates a Table in Office Word?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! I was looking through the Office Space Archive<\/a> and came across a nice article that talks about how to create a table in Office Word<\/a>. 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?<\/p>\n

– DC<\/p>\n

\"Spacer\"\"Hey, <\/p>\n

Hi DC,<\/p>\n

You know, the great thing about Tech\u00b7Ed 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\u00b7Ed in Los Angeles is there are world-class restaurants\u2014and don\u2019t 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<\/a> Ed saw at the San Diego Zoo<\/a> who may have attended one Tech\u00b7Ed session too many.<\/p>\n

\"Image <\/p>\n

 <\/p>\n\n<\/thead>\n\n\n
\n

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<\/a>. This is included as Appendix C in the Microsoft Press book, Microsoft Windows PowerShell Step by Step<\/a>. It is also in the Windows PowerShell Graphical Help File<\/a>. Clearly, we are proud of that thing. You may also want to check out our Windows PowerShell Scripting Hub<\/a> 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<\/a>. 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.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

The GetServiceCreateWordTable.vbs<\/b> from the Office Space Creating Tables in Microsoft Word<\/a> article is shown here for ease of comparison:<\/p>\n

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 3<\/p>\n

Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()<\/p>\n

Set objRange = objDoc.Range()
objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS
Set objTable = objDoc.Tables(1)<\/p>\n

objTable.Cell(1, 1).Range.Text = “Service Name”
objTable.Cell(1, 2).Range.Text = “Display Name”
objTable.Cell(1, 3).Range.Text = “Service State”<\/p>\n

x = 2<\/p>\n

strComputer = “.”<\/p>\n

Set objWMIService = _
    <\/span>GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)
Set colItems = objWMIService.ExecQuery(“Select * from Win32_Service”)<\/p>\n

For Each objItem in colItems
    <\/span>objTable.Rows.Add()
    <\/span>objTable.Cell(x, 1).Range.Text = objItem.Name
    <\/span>objTable.Cell(x, 2).Range.Text = objItem.DisplayName
    <\/span>objTable.Cell(x, 3).Range.Text = objItem.State
    <\/span>x = x + 1
Next<\/p>\n

objTable.AutoFormat(9)<\/font><\/p>\n

The GetServiceCreateWordTable.ps1<\/b> Windows PowerShell version is an exact translation of the GetServiceCreateWordTable.vbs<\/b> 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<\/b> script, the two variables that were created are constants. In Windows PowerShell if you want to create a constant, you use the New-Variable<\/b> cmdlet as shown here:<\/p>\n

PS C:\\> New-Variable -Name Number_Of_Rows -Value 1 -Option constant<\/font><\/span><\/p>\n

If you try to change the value of the $Number_Of_Rows<\/b> constant, you will receive an error message as shown here:<\/p>\n

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  <\/span><<<< = 5<\/font><\/span><\/p>\n\n\n\n<\/pre>\n
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:<\/p>\n
$Number_Of_Rows = 1
$Number_Of_Columns = 3<\/font><\/span><\/p>\n
Every script that works with Word Automation must create an instance of the Word.Application object<\/a>. In Windows PowerShell, you use the New-Object<\/b> cmdlet specify the comobject<\/b> parameter and supply the name of the Program ID. This is shown here:<\/p>\n
$objWord = New-Object -comobject word.application<\/font><\/span><\/p>\n
After the Word.Application<\/b> object is created, the GetServiceCreateWordTable.vbs<\/b> script sets the visible property to $true<\/b>, adds a document, and creates a new range object<\/a>. For more information about this, look at Monday\u2019s Hey, Scripting Guy article<\/a>. This section of code is seen here:<\/p>\n
$objWord.Visible = $true
$objDoc = $objWord.Documents.Add()
$objRange = $objDoc.Range()<\/font><\/span><\/p>\n
The next step is to add a table. To do this, you use the Add method<\/a> from the Tables collection<\/a>. The Add<\/b> method is shown in Table 1<\/b>. The Add<\/b> method has three required parameters: Range<\/b>, NumRows<\/b>, and NumColumns<\/b>. The GetServiceCreateWordTable.vbs<\/b> 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:<\/p>\n
$objDoc.Tables.Add($objRange,$Number_Of_Rows,$Number_Of_Columns)<\/font><\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n
Table 1 The Add method<\/td>\n<\/tr>\n
Name<\/td>\nRequired\/Optional<\/td>\nData Type<\/td>\nDescription<\/td>\n<\/tr>\n<\/thead>\n
\n
Range<\/p>\n<\/td>\n
\n
Required<\/p>\n<\/td>\n
\n
Range object<\/p>\n<\/td>\n
\n
The range where you want the table to appear. The table replaces the range, if the range is not collapsed.<\/p>\n<\/td>\n<\/tr>\n
\n
NumRows<\/p>\n<\/td>\n
\n
Required<\/p>\n<\/td>\n
\n
Long<\/p>\n<\/td>\n
\n
The number of rows that you want to include in the table.<\/p>\n<\/td>\n<\/tr>\n
\n
NumColumns<\/p>\n<\/td>\n
\n
Required<\/p>\n<\/td>\n
\n
Long<\/p>\n<\/td>\n
\n
The number of columns that you want to include in the table.<\/p>\n<\/td>\n<\/tr>\n
\n
DefaultTableBehavior<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
Sets a value that specifies whether Microsoft Word automatically resizes cells in tables to fit the cells\u2019 contents (AutoFit). Can be either of the following constants: wdWord8TableBehavior<\/b> (AutoFit disabled) or wdWord9TableBehavior<\/b> (AutoFit enabled). The default constant is wdWord8TableBehavior<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
AutoFitBehavior<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
Sets the AutoFit rules for how Word sizes tables. Can be one of the WdAutoFitBehavior<\/b> constants.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The GetServiceCreateWordTable.vbs<\/b> script next retrieves the first table from the Tables<\/b> collection. The VBScript line of code is seen here:<\/p>\n
Set objTable = objDoc.Tables(1)<\/font><\/span><\/p>\n
In Windows PowerShell this is the code to retrieve the first table from the Tables<\/b> collection:<\/p>\n
$objTable = $objDoc.Tables.item(1)<\/font><\/span><\/p>\n
VBScript requires you to use the Set<\/b> statement when a method returns an object. The confusing section of the script is the VBScript code that calls the Item method<\/a> from the Tables collection<\/a>. You might not know this because the Item<\/b> method is the default method from the Tables<\/b> 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<\/b> method or even querying a Tables<\/b> property from a document<\/b> object. When you run the GetServiceCreateWordTable.ps1<\/b> 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.<\/p>\n
The table column headings are produced by using the Cell method<\/a> from the Table object<\/a>. The Cell<\/b> method returns a Cell object<\/a>. The range<\/b> property from the cell<\/b> object returns a Range object<\/a>. The range object has a Text property<\/a> that enables you to set or retrieve text from the Range<\/b> object. This is shown here:<\/p>\n
$objTable.Cell(1,1).Range.Text = “Service Name”
$objTable.Cell(1,2).Range.Text = “Display Name”
$objTable.Cell(1,3).Range.Text = “Service State”<\/font><\/span><\/p>\n
The GetServiceCreateWordTable.vbs<\/b> script assigns the number 2 to the X<\/b> 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:<\/p>\n
x = 2<\/p>\n
strComputer = “.”<\/p>\n
Set objWMIService = _
    <\/span>GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)
Set colItems = objWMIService.ExecQuery(“Select * from Win32_Service”)<\/font><\/span><\/p>\n
In the GetServiceCreateWordTable.ps1<\/b> script, this section of the script is simplified because of the use of the Get-WmiObject<\/b> cmdlet. This is seen here:<\/p>\n
$x = 2
$strComputer = “.”<\/p>\n
Get-WmiObject -query “Select * from Win32_Service” -computername $strComputer<\/font><\/span><\/p>\n
Because we are doing a script translation, we decided to use the legacy WQL syntax with the Get-WmiObject<\/b>. This compatability mode of using Windows PowerShell is discussed in How do I migrate my VBScript WMI queries to Windows Powershell<\/a>. If we were writing this script from scratch, we would query the Win32_Service<\/b> class directly:<\/p>\n
Get-WmiObject \u2013class Win32_Service \u2013computername $strComputer<\/font><\/span><\/p>\n
The GetServiceCreateWordTable.vbs<\/b> script stores the WMI service information in the colItems<\/b> variable and uses the For\u2026Each\u2026Next<\/b> 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<\/b> property of the Range<\/b> object in the current cell range. When it is finished writing to the current cells, it increments the value of the x<\/b> variable and continues to the next objItem<\/b> in the collection. This is shown here:<\/p>\n
For Each objItem in colItems
    <\/span>objTable.Rows.Add()
    <\/span>objTable.Cell(x, 1).Range.Text = objItem.Name
    <\/span>objTable.Cell(x, 2).Range.Text = objItem.DisplayName
    <\/span>objTable.Cell(x, 3).Range.Text = objItem.State
    <\/span>x = x + 1
Next<\/font><\/span><\/p>\n
The GetServiceCreateWordTable.ps1<\/b> script does not use the ForEach<\/b> 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<\/b> cmdlet. Inside the script block of the ForEach-Object<\/b> cmdlet the script adds a new row, writes to the text<\/b> property of the Range<\/b> object of the Cell<\/b> object, and uses the $_<\/b> 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<\/b> variable by using the ++<\/b> operator. This section of code is shown here:<\/p>\n
Foreach-Object {
 <\/span>$objTable.Rows.Add()
 <\/span>$objTable.Cell($x,1).Range.Text = $_.name
 <\/span>$objTable.Cell($x,2).Range.Text=$_.DisplayName
 <\/span>$objTable.Cell($x,3).Range.Text=$_.State
 <\/span>$x++
} #end Foreach-Object<\/font><\/span><\/p>\n
The last thing that must be done is to format that Word table. The GetServiceCreateWordTable.ps1<\/b> script provides an enumeration value directly to the AutoFormat method<\/a> from the Table<\/b> object. The Format<\/b> parameter will accept either a WdTableFormat<\/a> enumeration value or a WdTableFormatApply<\/a> enumeration value. The number 9 corresponds to the wdTableFormatColorful2<\/b> format. Colorful<\/i> is a good name for table format number 9. You can see it for yourself here:<\/p>\n
\"Image <\/p>\n
 <\/p>\n
The parameters of the AutoFormat<\/b> method from the Table<\/b> object are discussed in Table 2<\/b>.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Table 2 The parameters of the AutoFormat method from the Table object<\/td>\n<\/tr>\n
Name<\/td>\nRequired\/Optional<\/td>\nData Type<\/td>\nDescription<\/td>\n<\/tr>\n<\/thead>\n
\n
Format<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
The format to apply. This parameter can be a WdTableFormat<\/b> constant, a WdTableFormatApply<\/b> constant, or a TableStyle<\/b> object.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyBorder<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True to apply the border properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyShading<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the shading properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyFont<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the font properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyColor<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the color properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyHeadingRows<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the heading-row properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyLastRow<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the last-row properties of the specified format. The default value is False<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyFirstColumn<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the first-column properties of the specified format. The default value is True<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyLastColumn<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> to apply the last-column properties of the specified format. The default value is False<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
AutoFit<\/p>\n<\/td>\n
\n
Optional<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
True<\/b> 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<\/b>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The GetServiceCreateWordTable.ps1<\/b> script is shown here:<\/p>\n
$Number_Of_Rows = 1
$Number_Of_Columns = 3<\/p>\n
$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)<\/p>\n
$objTable.Cell(1,1).Range.Text = “Service Name”
$objTable.Cell(1,2).Range.Text = “Display Name”
$objTable.Cell(1,3).Range.Text = “Service State”<\/p>\n
$x = 2
$strComputer = “.”<\/p>\n
Get-WmiObject -query “Select * from Win32_Service” -computername $strComputer |
Foreach-Object {
 <\/span>$objTable.Rows.Add()
 <\/span>$objTable.Cell($x,1).Range.Text = $_.name
 <\/span>$objTable.Cell($x,2).Range.Text=$_.DisplayName
 <\/span>$objTable.Cell($x,3).Range.Text=$_.State
 <\/span>$x++
} #end Foreach-Object<\/p>\n
$objTable.AutoFormat(9)<\/font><\/p>\n
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.<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[84,49,3,4,5,155,45],"class_list":["post-53763","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-word","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-vbscript","tag-vbscript-migration","tag-windows-powershell"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/53763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=53763"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/53763\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=53763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=53763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=53763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}