Call Word vba Macro from PowerShell

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:

1. 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!