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:
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!
