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 Table
Dim 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 With
Next
End 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!

Comments (0)

Skip to main content