Powershell-Word Table Rows

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Windows LiveUser136 Windows LiveUser136 1 month, 1 week ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #50736

    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

    #50849
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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().

    #50920

    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

    #51155
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
    #51176

    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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.