none
Embed Object in Specific Cell In Table in a Word Document Using VBA RRS feed

  • Question

  • I need a macro to insert variable of powershell to word table.
    This code create a new word document and insert data.

    $VerbosePreference = "Continue"
    $script_path = Split-Path -parent $MyInvocation.MyCommand.Definition

    function test_doc
    {
    [CmdletBinding()]
    Param
        (
            [Parameter(Mandatory=$true,
                       ValueFromPipelineByPropertyName=$true,
                       Position=0)]
            $sum,
            [int]
            $comm,
            [int]
            $sumreturn,
            [int]
            $total
        )
    $doc_file=join-path -path $script_path -childpath "TestDoc2.doc"
    $objWord = New-Object -comobject Word.Application 
    $objWord.Visible = $True
    $objDoc = $objWord.Documents.Open($doc_file)
    $objSelection = $objWord.Selection

    $ReplaceAll = 4
    $FindContinue = 1
    $MatchCase = $False
    $MatchWholeWord = $True
    $MatchWildcards = $False
    $MatchSoundsLike = $False
    $MatchAllWordForms = $False
    $Forward = $True
    $Wrap = $FindContinue
    $Format = $False

    $FindText = "{sum}"
    $ReplaceText = $sum

    $objSelection.Find.Execute($FindText,$MatchCase,
      $MatchWholeWord,$MatchWildcards,$MatchSoundsLike,
      $MatchAllWordForms,$Forward,$Wrap,$Format,
      $ReplaceText,$ReplaceAll)

    $FindText = "{comm}"
    $ReplaceText = "$comm"

    $objSelection.Find.Execute($FindText,$MatchCase,
      $MatchWholeWord,$MatchWildcards,$MatchSoundsLike,
      $MatchAllWordForms,$Forward,$Wrap,$Format,
      $ReplaceText,$ReplaceAll)
        
      $FindText = "{sumreturn}"
    $ReplaceText = "$sumreturn"

    $objSelection.Find.Execute($FindText,$MatchCase,
      $MatchWholeWord,$MatchWildcards,$MatchSoundsLike,
      $MatchAllWordForms,$Forward,$Wrap,$Format,
      $ReplaceText,$ReplaceAll)

     $FindText = "{total}"
    $ReplaceText = "$total"

    $objSelection.Find.Execute($FindText,$MatchCase,
      $MatchWholeWord,$MatchWildcards,$MatchSoundsLike,
      $MatchAllWordForms,$Forward,$Wrap,$Format,
      $ReplaceText,$ReplaceAll)

    $objWord.ActiveDocument.InlineShapes.item(1).chart.ChartData.Workbook.ActiveSheet.cells.item(4,4)=$SOnline


         Remove-Variable -Name objWord
         [gc]::collect()
         [gc]::WaitForPendingFinalizers()

    }

    test_doc -sum $sum -comm $comm -sumreturn $sumreturn -total $total
    Saturday, April 23, 2016 1:12 PM

Answers

  • Hi,

    Thanks for posting in Word forum.

    This forum is for general discussion for Word feature and settings. Since the issue is code related,I would move it to Word for developer forum.

    Thanks for your understanding.


    Regards,

    Winnie Liang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Marked as answer by Aleks Roth Tuesday, April 26, 2016 11:19 AM
    Tuesday, April 26, 2016 1:52 AM

All replies

  • It appears you're trying to update an embedded chart object. If so, you must Activate it before you can edit its contents. For example:

    With $objWord.ActiveDocument.InlineShapes.Item(1).Chart.ChartData
        .Activate
        .Workbook.Worksheets.Item(1).Cells(4, 4).Value = $SOnline
        .Workbook.Application.Quit
    End with

    Do note that '.Workbook.Application.Quit' only works with Office 2007 & later. You may need to edit the code slightly for it to work with PowerShell.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, April 25, 2016 4:56 AM
  • No, I paste data, value variable powershell this text. Word Creates a document template.

    Monday, April 25, 2016 12:19 PM
  • Your reply has no connection I can see with your thread title, your first post or my response to it.

    Your code line:
    $objWord.ActiveDocument.InlineShapes.item(1).chart.ChartData.Workbook.ActiveSheet.cells.item(4,4)=$SOnline
    will not work. My response shows how you need to approach that issue. If you have a different issue, you need to explain what it is.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, April 25, 2016 1:09 PM
  • Hi,

    Thanks for posting in Word forum.

    This forum is for general discussion for Word feature and settings. Since the issue is code related,I would move it to Word for developer forum.

    Thanks for your understanding.


    Regards,

    Winnie Liang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Marked as answer by Aleks Roth Tuesday, April 26, 2016 11:19 AM
    Tuesday, April 26, 2016 1:52 AM
  • Hi,

    Have you fixed this issue now? If not, please feel free let us know which block you.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 27, 2016 11:41 AM
    Moderator