Share via


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:

image

 

Steps:

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

    image

Click on Macros and create new Macro and store Macro
image

image

Add Macro code to DeleteTableParam Macro

image

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
image

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

image

Keep in mind: string is casesensitive!

Save updated Word document.

image

End Result:

image

Have fun!