Populating a table in Word

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 7 months ago.

  • Author
    Posts
  • #6060

    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.

You must be logged in to reply to this topic.