Author Posts

January 1, 2012 at 12:00 am

by rambog at 2013-03-22 07:51:50

I have a template in which I need to populate with server information. I am able to access the table but, for some reason, a couple of the columns only have one letter while a couple of others have the information intact. It appears that the columns where only partial information is contained is somehow wrapping the next letter onto the next row. I have mucked with the word warpping of the dotx file to now avail. I am looking at how to properly fit the server names into all the columns of my table. Any suggestions are welcome.

Here is the pertinent section of code:
#Now, let's generate the surveys
#first, specify location under My Documents
$MyDocuments=[Environment]::GetFolderPath("MyDocuments")
#$DebugPreference="Continue"
#$ErrorActionPreference="Continue"
#The information will strictly arise from the csv file and not vmware
#gather the unique occurrances of mnemonics from csv file
$list=Import-csv serverreport.csv
$mnemonics=$list|select mnemonic|get-unique -AsString|foreach {$_.Mnemonic}
Write-Debug "`$mnemonics=$mnemonics"
#Step thru each Mnemonic in the CSV file and create a separate word doc
foreach ($app in $mnemonics) {
$word=new-object -ComObject "Word.Application"
#here is the template file to be used
$doc=$word.documents.Add("$MyDocuments\ServerInfo\ServerInventory.dotx")
Write-Debug "`$app=$app"
#assemble all the VMs based upon their roles
$testserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'Test'}|select Servername|foreach {$_.Servername}
Write-Debug "`$testserverlist=$testserverlist"
Write-Debug "Number of test servers=$testserverlist.count"
$qaserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'QA'}|select Servername|foreach {$_.Servername}
Write-Debug "`$qaserverlist=$qaserverlist"
Write-Debug "Number of QA servers=$qaserverlist.count"
$prodserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'Production'}|select Servername|foreach {$_.Servername}
Write-Debug "`$prodserverlist=$prodserverlist"
Write-Debug "Number of Prod servers=$prodserverlist.count"
$drserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'Disaster Recovery'}|select Servername|foreach {$_.Servername}
Write-Debug "`$drserverlist=$drserverlist"
Write-Debug "Number of DR servers=$drserverlist.count"
#need to get the amount of rows needed in table from higer value of test,qa,prod or dr servers(used to expand the table, if necessary)
if ($testserverlist.count -ge $qaserverlist.count) {
$numberofrows=$testserverlist.count}
else {$numberofrows=$qaserverlist.count}
if ($numberofrows -lt $prodserverlist.count) {
$numberofrows=$prodserverlist.count}
if ($numberofrows -lt $drserverlist.count) {
$numberofrows=$prodserverlist.count}

$AppTeam=$doc.Bookmarks.Item("Text128").Range
$AppTeam.Text="$app"
$ATLperson=$doc.Bookmarks.Item("AppTechLead").Range
$ATLperson.Text=$List|where {$_.Mnemonic -eq $app}|select ATL|get-unique -AsString|foreach {$_.ATL}
#Insert the server information into table
$servertable=$word.ActiveDocument.Tables.item(1)
#The server entries begin at the 5th row of the table and the server name entries go from column 2 through 5 on each row
$tablerowposition=5
for ($j=1; $j -le $numberofrows; $j++) {
$servertable.Cell($tablerowposition,2).Range.Text=$testserverlist[$j-1]
$servertable.Cell($tablerowposition,3).Range.Text=$qaserverlist[$j-1]
$servertable.Cell($tablerowposition,4).Range.Text=$prodserverlist[$j-1]
$servertable.Cell($tablerowposition,5).Range.Text=$drserverlist[$j-1]
$tablerowposition=$tablerowposition + 1
#add rows if needed to accomodate larger server environments
if ($tablerowposition -ge 9) {
$servertable.rows.add()}
} #end creation of table for servers

#Finish the document and close
$doc.SaveAs([ref]"$MyDocuments\Serverinfo\$app-test.docx")
$doc.Close()
$Word.quit()
} #end foreach Mnemonic

Here is what my output looks like (in the existing table within word):
Test Environment QA Environment Production Environment DR Environment
s server004 server001 s
e server005 server002 e

When it should look like the following:
Test Environment QA Environment Production Environment DR Environment
server00a server004 server001 server003
server005 server002

by ArtB0514 at 2013-03-22 12:59:25

Your code is pretty hard to read as is. Can you use the PowerShell or Code buttons to help?

Are you sure that $TestServerList contains the correct information? Try using this code segment to verify:
$testserverlist=@($list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'Test'}|select -ExpandProperty Servername)
"Number of test servers=$($testserverlist.Count)"

The @() forces the results to be an array, even if 0 or 1 objects are returned. Neither $Null nor a simple string have a Count property, so they will each return $null instead of 0 or 1.

To get a property of an object in a string, you need to tell PowerShell to evaluate it by enclosing the expression in $().

by rambog at 2013-03-27 13:23:03

You solved my problem. In solving it, however, I discovered that servername would spill over into another section of the table in *.dotx. In other words, it appears to all be one table and I need the rows to be added after the first "section" (see section "A1" in attached *.jpg) and leave the other portion of the table alone.

Unfotunately, the place where I am working has blocked all photo sharing sites so I cannot show you what the table in the template looks like before and after the script.