Learn about Windows PowerShell
Summary: Microsoft Scripting Guy, Ed Wilson, shows how to use Windows PowerShell to create a Word table that shows different fonts.
Microsoft Scripting Guy, Ed Wilson, is here. We are half finished with Microsoft TechEd 2012 in Orlando, Florida. Last night was Community Night, and it was great to host dozens of scripters at the Scripting Guys table with the Scripting Wife and Daniel Cruz. It was awesome to be able to talk with both beginning and advanced scripters. Actually, in some respects, beginners to Windows PowerShell have some advantage over people who have been doing it for a while. The reason is that they may see new techniques, and they are still asking, “What if…” as in “What if I do this?” I am still in “what if” mode. In fact, I have never left “what if” mode. I learn so much cool stuff by just experimenting, playing, and wondering what would happen if I did something as opposed to doing something else.
Today’s script is a case-in-point. The reason is that what should have been a relatively easy script, ended up morphing in to a script that took me nearly two hours to complete. The reason will become obvious later. Because I was offline when I wrote this script, I did not have access to the Bing search engine, and therefore, I could not “cheat” to see if someone else had seen the problem and arrived at a solution. Luckily, I know how to use the Get-Member cmdlet, and could examine the returning objects to find a solution. I also know how to read error messages (I actually get a lot of practice reading error messages). With the members of an object and an error message that tells me why something does not work, I do not need to “cheat” to get my script to work properly.
The CreateWordSymbolTableFile.ps1 script is pretty long, but it has a lot of repetition. It could be made more compact, and it could probably be optimized, but I will leave that as an exercise for the reader (meaning that I am bored with this script now—it does what I need it to do, and I have other things to try).
The first thing I do is create two variables that I use later in the script when I create the table. The two variables hold the number of rows and the number of columns to use when creating the table. This is just like when you insert a table into a Word document—you specify the initial size of the table. You are not limited to that size because you can always add rows or columns to the table later. The initial settings only determine how the table will first appear. I usually try to ascertain how many columns I will need for my table, but I generally only add a couple of rows to the table because I do not always know how much data I will represent in the table. To save a bit of space in the script, I use a semicolon (;) to separate the two variable assignments and to permit me to use a single line. This line of code is shown here.
$rows = 2; $columns = 5
Now we come to the same three lines of code that generally inhabit most Microsoft Word automation scripts. For a discussion about these lines of code, see Monday’s blog Add a Symbol to a Word Document by Using PowerShell.
The three lines that I used to create the Word application object, make it visible, and add a document object to the application are shown here.
$word = New-Object -ComObject word.application
$word.visible = $true
$document = $word.documents.add()
Now I add a Range object to the script. Typically, you will work with a Range object or a Selection object. Yesterday, I used the Selection object, and on Monday, I used a Range object. Both of these objects expose the InsertSymbols method. The following line of code creates a new Range object, and stores the returned object in a variable named $range.
$range = $document.Range()
Now I need to add a table to the document. To do this, I use the Tables property to return a tables collection. From the tables collection, I use the Add method to add a new table to the document object that is stored in the $document variable. When adding a table, I need to supply the range object to host the table, the number of rows, and the number of columns. I stored the number of rows in the $rows variable and the number of columns in the $columns variable. When adding a table to the document, a Table object returns. I will use this object to work with the table later in the script. Therefore, I store the returned Table object in a variable named $table. This line of code is shown here.
$table = $document.Tables.add($range,$rows,$columns)
The next thing I need to do is use the newly created table and add the first row of text to serve as column headings. To do this, I use the Text property from the Range object to represent each cell in the first row of the table. I use the Cell method from the Table object to retrieve a specific cell in the table. The first number represents the row, and the second number is the column number. When I have the cell, I use the Range property to obtain the Range object for the specific cell. Adding text to the cell is as easy as assigning a value to the Text property. The code to add the first row of text that will be the column headings is shown here.
$table.cell(1,1).range.text = "code"
$table.cell(1,2).range.text = "wingdings"
$table.cell(1,3).range.text = "wingdings2"
$table.cell(1,4).range.text = "wingdings3"
$table.cell(1,5).range.text = "courier new"
With the first row of the table complete, it is time to add the remaining information to the table. The first thing to do is to create a new variable and initialize it equal to 2. This is because the first row contains the column headings, and therefore, all the new data to write to the table begins on the second row. I now use the for statement to create a loop that will create numbers 33 – 255 to use for the character codes that will be added to the table later in the script. I then use the Add method to add a new row to the table. I do not need to do anything with the row itself, so I pipe the returned Row object to the Out-Null cmdlet. This portion of the code is shown here.
For($i = 33 ; $i -le 255; $i++)
$table.rows.add() | out-null
The first column of the new row is easy. I use the Cell method to return the Cell object that represents the first column of the new row. I then use the Range property to return a Range object that represents that cell. Next I use the Text property of the Range object, and I assign the number stored in the $i variable to the cell. This code is shown here.
$Table.Cell($j,1).range.Text = $i
Now, things begin to get tricky. This is because I am unable to use the InsertSymbol method directly from the Range object that was returned from Cell object. It seems that I should be able to use a line of code such as the one shown here.
Unfortunately, an error arises when I attempt to use the InsertSymbol method. The error states that the InsertSymbol method is unavailable because the object does not refer to a simple range or selection. The complete error appears here.
Exception calling "InsertSymbol" with "2" argument(s): "This method or property
is not available because the object does not refer to simple range or selection."
At C:\data\ScriptingGuys\2012\HSG_6_11_12\CreateWordSymbolTableFile.ps1:27 char:38
+ $Table.Cell($j,1).range.InsertSymbol <<<< ($i,"WingDings")
+ CategoryInfo : NotSpecified: (:) , MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
It seems like I tried everything to work around the error. Eventually, I decided that my best approach would be to attempt to change the type of range that is returned by the Range property. To do this, I decided I would collapse the range by calling the Collapse method. This allowed me to reference a simple range and permitted me to make it past the error. Unfortunately, it also caused me to use three lines of code to do what I had hoped would only take a single line of code. Here are the three lines of code that obtain a range that represents a specific cell in the table, collapse the range, and use the range to insert a symbol into the cell.
$cellRange = $Table.Cell($j,2).range
When I confirmed that the three lines of code worked as desired, the remainder of the script was really copy, paste, and edit. The only thing that is not direct copy, paste, and edit is the little code $j++, which increments the value of the $j variable that is used to keep track of the row number for assigning the new values to the newly created cells in the new row. The remainder of the code is shown here.
$cellrange = $table.cell($j,4).range
$cellrange = $table.cell($j,5).range
The complete CreateWordSymbolTableFile.ps1 script is shown here.
$Table.Cell($j,1).range.Text = $i
$cellRange = $Table.Cell($j,2).range
$cellrange = $table.cell($j,3).range
When the previous script runs, the output in the image that follows appears. One thing I learned from this exercise is that wingdings2, wingdings3, and “normal” fonts have a lot of overlap in the actual symbols that they contain. Looking at a document like this is a great way to compare multiple fonts to help you better make comparisons. With the large number of font files shipping in Microsoft Office products, this script can become a very useful tool.
Microsoft Word Automation Week will continue tomorrow when I will talk about more Windows PowerShell cool stuff.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at email@example.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Hi - and thanks. - great post.
Is there a way to set the first line of the table as header ? I have tried to find it, but cant.