Call Word vba Macro from PowerShell

Article
03/15/2016

This week I was asked to help with removing tables in a large Word (docx) document when tables contained some string value in one of there cells from PowerShell.

I looked at many options to manipulate Word documents from PowerShell but none of them offered any help doing that directly. I ended up creating a Word VBA Macro and calling that Macro from PowerShell.

Because I spent quite some time developing this solution I thought I would share it with all of you.

Scenario:

Remove all Tables from Word document containing the string value ‘Netherlands’ calling Word Macro from PowerShell

Screenshot document:

Steps:

Create Word VBA Marcro in TableDocument.docx
Click on Developer in Word Document

Click on Macros and create new Macro and store Macro

Add Macro code to DeleteTableParam Macro

Marco code:

Sub DeleteTableParam(Str As String)Dim oTbl As TableDim oRng As Range

For Each oTbl In ActiveDocument.Tables    Set oRng = oTbl.Range        With oRng    .Find.Execute Findtext:=Str & "*", MatchWildcards:=True    If .Find.Found Then        oTbl.Delete        End If    End WithNextEnd Sub

Save Macro

Save and Close Word document

2. Call Macro from PowerShell

# ---------------------------------------------------# Script: C:\Users\stefstr\OneDrive - Microsoft\Scripts\PS\Word\RemoveTableFromWord.ps1# Version: 0.1# Author: Stefan Stranger# Date: 03/15/2016 15:21:38# Description: Remove tables from Word document calling Word Macro# Comments:# Changes: # Disclaimer: # This example is provided “AS IS” with no warranty expressed or implied. Run at your own risk. # **Always test in your lab first** Do this at your own risk!! # The author will not be held responsible for any damage you incur when making these changes!# ---------------------------------------------------

function Remove-TableFromWord{    [CmdletBinding()]    Param    (        [Parameter(Mandatory=$true,                   ValueFromPipelineByPropertyName=$true,                   Position=0)]        [string[]]$string,        [string]$filename    )

    Begin    {        Write-Verbose "Open Word Document $filename"        $wd = new-object -comobject word.application # create a com object interface (word application)        [void]$wd.documents.open($filename) # open doc

    }    Process    {                  Foreach ($str in $string) {            Write-Verbose "Removing table with string value: $str"            $wd.run("DeleteTableParam",$str) # exec macro named DeleteIssueIDTableParam with parameter IssueId        }

                  }    End    {        Write-Verbose "Save Word document $filename"        $wd.quit() # exit application     }}

PS C:\Users\stefstr> Remove-TableFromWord -string "Netherlands" -filename C:\temp\Tabledocument.docx -Verbose
VERBOSE: Open Word Document C:\temp\Tabledocument.docx
VERBOSE: Removing table with string value: Netherlands
VERBOSE: Save Word document C:\temp\Tabledocument.docx

Keep in mind: string is casesensitive!

Save updated Word document.

End Result:

Have fun!

Call Word vba Macro from PowerShell

Additional resources