Powershell-Word Table Rows

PowerShell Help
1

I have a script that worked a few months ago but no longer works. it collects values from SQL queries and adds them to word tables. after the script runs I check the variables and they have the data in them so its not the variables. this is how im adding to word table

$selection.Style="Heading 1"
$Selection.TypeText("Tools Reports")
$Selection.TypeParagraph()
$selection.Style="Heading 2"
$Selection.TypeText("HPSim Data")
$Selection.TypeParagraph()
$selection.Style="Normal"
$Selection.TypeText("The table below shows the status of HPSim Agents at Braintree AD")
$Selection.TypeParagraph()
$selection.Style="Normal"
$objRange = $Selection.Range
$UserTable =$Word.ActiveDocument.Tables.Add($Word.Selection.Range, 1, 4) 
$UserTable = $objDoc.Tables.Item(1)
$UserTable.Cell(1,1).Range.Text = "Measured Item"
$UserTable.Cell(1,2).Range.Text = $A1
$UserTable.Cell(1,3).Range.Text = $A2
$UserTable.Cell(1,4).Range.Text = $A3
$UserTable.Rows.Add()
$UserTable.Cell(2,1).Range.Text = "Servers in Active Directory"
$UserTable.Cell(2,2).Range.Text = $B1
$UserTable.Cell(2,3).Range.Text = $B2
$UserTable.Cell(2,4).Range.Text = $B3
$UserTable.Rows.Add()
$UserTable.Cell(3,1).Range.Text = "Servers Audited"
$UserTable.Cell(3,2).Range.Text = $C1
$UserTable.Cell(3,3).Range.Text = $C2
$UserTable.Cell(3,4).Range.Text = $C3
$UserTable.Rows.Add()
$UserTable.Cell(4,1).Range.Text = "HPSim Running"
$UserTable.Cell(4,2).Range.Text = $D1
$UserTable.Cell(4,3).Range.Text = $D2
$UserTable.Cell(4,4).Range.Text = $D3
$UserTable.Rows.Add()
$UserTable.Cell(5,1).Range.Text = "% of AD Servers with HPSim Running"
$UserTable.Cell(5,2).Range.Text = $E1
$UserTable.Cell(5,3).Range.Text = $E2
$UserTable.Cell(5,4).Range.Text = $E3
$UserTable.Rows.Add()
$UserTable.Cell(6,1).Range.Text = "% of Audited Servers with HPSim Running"
$UserTable.Cell(6,2).Range.Text = $F1
$UserTable.Cell(6,3).Range.Text = $F2
$UserTable.Cell(6,4).Range.Text = $F3
$Selection.EndKey($END_OF_STORY)
$selection.MoveDown()
$UserTable.AutoFormat(23)
$UserTable.Columns.AutoFit()
$Selection.TypeParagraph()

the first field of the first row populates but SECOND FIELDS are blank and I get error

I derive the values for $A AND $A1 USING

$A = Invoke-SqlQuery -Query “SELECT COUNT(Name) FROM adcompsbraintree” -Server “CLUSQL423IIT\IITDB” -Database ‘ExcelData’
$A=$A.‘Column#0’
$APERCENT = $A/$A
$A1= “{0:P}” -f $APercent

the error im getting for each is

Specified cast is not valid.
At C:\DataReporting\Resources\EssexWeeklyReport.ps1:2418 char:1

  • $UserTable.Cell(2,3).Range.Text = $A1
  •   + CategoryInfo          : OperationStopped: (:) [], InvalidCastException
  + FullyQualifiedErrorId : System.InvalidCastException
2

Cast not valid indicates that the value you are attempting to set in the cell is a different data type. For instance, if you had the cell format data type to be a float and you passed “Hello”, you’d get an error that it doesn’t know how to cast (or convert) Hello to a float value. To start troubleshooting, I would remove any formatting ({0:P}") and use the plain text or try $A1.ToString().

3

Hi and thanks I find if I use

$A = Invoke-SqlQuery -Query “SELECT COUNT(Name) FROM adcompsxxxxxx” -Server “CLUSQL423IIT\IITDB” -Database ‘ExcelData’
$A=$A.‘Column#0’
$A1 = ($A/$A)*100

then the table populates with the percentage but I have tried to round it up to 2 decimal places and the column is empty again
$A = Invoke-SqlQuery -Query “SELECT COUNT(Name) FROM adcompsxxxxxx” -Server “CLUSQL423IIT\IITDB” -Database ‘ExcelData’
$A=$A.‘Column#0’
$A1 = ($A/$A)*100
$A1= [System.Math]::Round($A1, 2)

can you help? Im a beginner and quite lost

4

Can you tell me why exactly you are using Word for reporting? How is the report being distributed? Email? There are much easier ways to generate reports in Powershell than to manipulate Word or Excel, such as HTML. The API’s for Office can be a bit kludgy and haven’t been updated years. It’s hard to troubleshoot what is going on, but my guess is you are attempting to put a data type in the cell that it doesn’t like. You can see the data type by using .GetType() (e.g. $A1.GetType() ).

Also, some other small suggestions. A simple AS statement to return a named column so that others that read your script understand what count you are getting:

SELECT COUNT(Name) As WhateverCount FROM adcompsxxxxxx
5

Simple answer is Contractual obligations. Thanks for your reply and suggestion but I have just figured out the problem. Our IT department had forced windows management framework 5 onto my machine. I have uninstalled it and all works as it should now.

Many thanks