Powershell-Word Table Rows

Welcome Forums General PowerShell Q&A Powershell-Word Table Rows

Viewing 4 reply threads
  • Author
    Posts
    • #50736
      Participant
      Topics: 2
      Replies: 4
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 8
      Replies: 1271
      Points: 1,020
      Helping Hand
      Rank: Community Hero

      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
      Participant
      Topics: 2
      Replies: 4
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 8
      Replies: 1271
      Points: 1,020
      Helping Hand
      Rank: Community Hero

      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
      Participant
      Topics: 2
      Replies: 4
      Points: 0
      Rank: Member

      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 4 reply threads
  • The topic ‘Powershell-Word Table Rows’ is closed to new replies.