Hey, Scripting Guy! Question

Hey, Scripting Guy! I enjoyed talking to you yesterday at TechEd. Unfortunately, I did not think about this until I came back to my hotel room and read your "Hey, Scripting Guy!" column. It seems to me that you spent much time saying there was not much of a reason to translate a VBScript into Windows PowerShell, and then you went ahead and translated a VBScript into Windows PowerShell. What is the deal? I know it was late when I read your article, and it is even later now, but still I am not sure that I have the point. Did I miss something?

- CY

SpacerHey, Scripting Guy! Answer

Hi CY,

Yes, you missed something. The meaning of life was clearly enumerated throughout the article. For a quick review it is 42, and you did not know we were capable of deep thought. In yesterday’s Hey, Scripting Guy! article, we did say that generally there is little reason to translate a VBScript into Windows PowerShell. Here are some reasons why you may want to translate a VBScript to Windows PowerShell:

For practice, because you are learning Windows PowerShell.

Because you are planning a major change to the script, and you do not want to maintain an old VBScript.

Because your company, (or you), has decided you do not want to maintain two scripting environments.

Because a feature your old script relies on is not available in newer editions of the software (example Exchange 2007 does not have the WMI classes used by Exchange 2003).

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.

Yesterday we translated the VBScript into Windows PowerShell. Today let's investigate reason number 2 and add more functionality to the hyperlink script. To start, here is the AddHyperlinkToWord.ps1 script we ended the day with yesterday.

AddHyperlinkToWord.ps1

$objWord = New-Object -comobject Word.Application
$objWord.Visible = $true
$objDoc = $objWord.Documents.Add()
$objRange = $objDoc.Range()
$objLink = 
$objDoc.HyperLinks.Add($objRange, "http://www.ScriptingGuys.com",$null,$null,"Script Center")

There are a couple of improvements we can make to the script to make it more useful. The first is to modify it so that it creates more than one hyperlink. The second improvement is to save the file. Those two improvements would make an immediate difference in our life. Whereas we are at it, we may as well add a little error handling to it, just in case the script falls into the wrong hands. We decided to call our new script, AddMultipleHyperlinksToWord.ps1. It is seen here.

AddMultipleHyperlinksToWord.ps1

Function New-WordDoc
{
 $script:objWord = New-Object -comobject Word.Application
 $objWord.Visible = $true
 $objWord.Documents.Add()
 $objWord.Selection
} #End New-WordObject

Function New-Selection
{
 Param($Doc)
 $Doc.Selection
} #end New-Selection

Function Import-Links
{
 Param($path)
 If(Test-Path -path $csvFile)
  {
   Import-Csv -path $path
  } # end Test-Path
} # end Import-Links

Function Add-HyperLinks
{
 Param($doc, $Range, $Url, $DisplayText)
 $objLink = $Doc.HyperLinks.Add($Range, $Url,$null,$null,$DisplayText)
} #end Add-HyperLinks

Function Save-Document
{
 Param($path, $doc)
 [ref]$saveFormat = "microsoft.office.interop.word.wdSaveFormat" -as [type]
 if(-not (Test-Path -path $path))
   {
   $doc.saveas([ref]$path, [ref]$saveformat::wdFormatDocument)
   $script:objWord.quit()
  } #end if
 Else
 {
  $script:objWord.visible = $false
  $script:objWord.quit()
  "$path already exists. Choose a different file name, and try again"
 } # end else
} # end Save-Document

# *** Entry Point to Script ***
$i=$j=0
$csvFile = "C:\Fso\Hyperlinks.csv"
$filePath = "c:\fso\hyper.doc"
$objDoc = New-WordDoc
$Selection = $objDoc[1]
$objDoc = $objDoc[0]

Import-Links -path $csvFile | 
Foreach-Object {
  Add-HyperLinks -Doc $objDoc -Range $Selection.Range -Url $_.Url -Display $_.DisplayText
  $Selection.TypeParagraph()
} #end Foreach-Object

Save-Document -path $filePath -doc $objDoc

In the AddMultipleHyperLinksToWord.ps1 script the first thing we do is to create a series of functions. The first function is the New-WordDoc function. This function creates a script level variable named $objWord that holds the Word.Application object. We then make the instance of Word visible, add a document to it, and create a selection object. The New-WordDoc function returns two objects. The first is a Document object, and the second is a selection object. This is seen here.

Function New-WordDoc
{
 $script:objWord = New-Object -comobject Word.Application
 $objWord.Visible = $true
 $objWord.Documents.Add()
 $objWord.Selection
} #End New-WordObject

The next function we create is the New-Selection function. This function takes one parameter, a document object. When you query the selection property of the document object, it returns a selection object. When you call the New-Selection function, you have to use a variable that will hold the selection object that is returned.

Function New-Selection
{
 Param($Doc)
 $Doc.Selection
} #end New-Selection

A comma separated value (CSV) file is used to create the hyperlinks. This makes it easy to change, to update, and to change the hyperlinks that are created. An example of a CSV file is seen here:

Image of an example of a CSV file

 

We use the Import-Links function to read the CSV file. We pass a single parameter to the Import-Links function, the path of the CSV file. We use the Test-Path cmdlet to determine whether the file exists. If it does we use the Import-CSV function to import the CSV file. This is seen here.

Function Import-Links
{
 Param($path)
 If(Test-Path -path $csvFile)
  {
   Import-Csv -path $path
  } # end Test-Path
} # end Import-Links

We now come to the Add-HyperLinks function. This function accepts four parameters. The doc parameter is an instance of a document object. The range parameter is a range object. The url parameter is a string that represents a url to a resource, and the last parameter is a string that becomes the test that you see in the document. The Add-Hyperlinks function is seen here.

Function Add-HyperLinks
{
 Param($doc, $Range, $Url, $DisplayText)
 $objLink = $Doc.HyperLinks.Add($Range, $Url,$null,$null,$DisplayText)
} #end Add-HyperLinks

The last function we create is the Save-Document function. This function accepts two parameters. The first is the path of the document that you want to create. The second object is a variable that contains a copy of the Document object. We then create an instance of the wdSaveFormat type class. We have talked about how to create this reference type before. We also talked about it when we wrote the script that creates PDF files. We use the wdSaveFormat reference type to enable us to save the Word document. As soon as we have saved the Word document, we exit the Word application. If the word document was previously saved, we prompt to save the file under a different name. This is seen here.

Function Save-Document
{
 Param($path, $doc)
 [ref]$saveFormat = "microsoft.office.interop.word.wdSaveFormat" -as [type]
 if(-not (Test-Path -path $path))
   {
   $doc.saveas([ref]$path, [ref]$saveformat::wdFormatDocument)
   $script:objWord.quit()
  } #end if
 Else
 {
  $script:objWord.visible = $false
  $script:objWord.quit()
  "$path already exists. Choose a different file name, and try again"
 } # end else
} # end Save-Document

In the entry point to the script we specify the path of the CSV file, and also the path of store the output document. We call the New-WordDoc function and store the array of objects that are returned in the $objDoc variable. The second object that is returned is an instance of a Selection object. It is contained in element 1 of the $objDoc array. The first object that is returned is an instance of a document object. It is contained in element 0 of the $objDoc array. We retrieve the document object from element 0 of the $objDoc array, and store it back in the variable $objDoc-so that it is no longer an array of objects. This is seen here.

$csvFile = "C:\Fso\Hyperlinks.csv"
$filePath = "c:\fso\hyper.doc"
$objDoc = New-WordDoc
$Selection = $objDoc[1]
$objDoc = $objDoc[0]

Now we have to read the CSV file. To do this, we use the Import-Links function and pass it the path of the CSV file. We pipeline the resulting CSV information to the Foreach-Object cmdlet and call the Add-HyperLinks function. This function takes a document object, a range object, a string for the url, and the display name. We then use the selection object, contained in the $selection variable, to create a blank paragraph between entries in the document. This is seen here.

Import-Links -path $csvFile | 
Foreach-Object {
  Add-HyperLinks -Doc $objDoc -Range $Selection.Range -Url $_.Url -Display $_.DisplayText
  $Selection.TypeParagraph()
} #end Foreach-Object

The last thing we do is call the Save-Document function. This is shown here.

Save-Document -path $filePath -doc $objDoc

The resulting document is seen here:

Image of the resulting document

 

CY, we definitely changed things around. All the changes would not have been needed if we only wanted to save the document, and handle multiple hyperlinks which were the main gist of the original requirements. We got a bit carried away. The good thing is the number of re-usable functions we created. We hope that you enjoyed the script. The three hour time zone difference between Charlotte and Los Angeles is starting to catch up with me. I should not complain, as there are people from all over the world who are more jet lagged than I am. It is one of the good things about TechEd-getting to meet people from all over the world. See you tomorrow. Take care.

 

Ed Wilson and Craig Liebendorfer, Scripting Guys