Powershell Template Replacement

README.md

replace.ps1

This is a script to replace placeholders like {name} in a Word document with their values as defined in an Excel spreadsheet.

Usage

1. Save replace.ps1 below somewhere on your machine.

2. Create a Word document with your message, for example:

   Hello {Name},
   Your balance is {balance}
   

3. Create an Excel document with your replacements, and a header row, for example:

   Name	Balance
   John Doe	$123.45
   Suzan Pevensie	$5456.70

4. Open PowerShell, you can either search for it or type powershell in the address bar in Windows Explorer.

5. PowerShell will display the path before the arrow designating your input. ~ means your home folder C:\Users\You and .. means one directory up. . means the current directory. Use cd to get to the directory you saved the script in.

   ~> cd Documents
   ~\Documents> cd ..
   ~> cd Desktop
   ~\Desktop>

6. Run the script with .\replace.ps1 You can either provide the arguments before hitting enter, or it will prompt you for them. You can use relative paths to the files instead of typing out the full path. If your files are all in the same directory, your call and the corresponding output could look like this. Note that in the output file name {Name}.docx, you can use the column names from your Excel spreadsheet.

   ~\Desktop\replace_test> .\replace.ps1 .\Template.docx .\Data.xlsx '{Name}.docx'
   Processing row 1/3
   Processing row 2/3
   Processing row 3/3
   ~\Desktop\replace_test>

7. Open the generated output files and confirm the script worked.

replace.ps1

# Define the inputs, these can either be passed in order, or by name
# By position:
#  .\replace.ps1 .\Template.docx .\Data.xlsx '{name}.docx'
# By name (order doesn't matter):
#  .\replace.ps1 -TemplateFile .\Template.docx -DataFile .\Data.xlsx -NameTemplate '{name}.docx'
Param(
    [Parameter(Mandatory=$true)] $TemplateFile,
    [Parameter(Mandatory=$true)] $DataFile,
    [Parameter(Mandatory=$true)] $NameTemplate,
    $OutDir='.'
)

# Word + Excel don't like relative paths, so make them absolute
$TemplateFile = (echo $TemplateFile | Resolve-Path).Path
$DataFile = (echo $DataFile | Resolve-Path).Path
$OutDir = (echo $OutDir| Resolve-Path).Path

# If the output type is PDF, we need to pass an output format
# https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.word.wdsaveformat?view=word-pia
$Format = 16 # docx
if ($NameTemplate -match ".pdf$") {
    $Format = 17 # pdf
}

# Open excel
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($DataFile, $false, $true)
$Range = $Workbook.Worksheets[1].UsedRange
$Header = $Range.Rows[1].Columns

# Open word
$Word = New-Object -ComObject Word.Application

# Helper function to replace text in the currently open word doc.
function ReplaceText {
    Param($Find, $Replace)

    $MatchCase = $False
    $MatchWholeWord = $False
    $MatchWildcards = $False
    $MatchSoundsLike = $False
    $MatchAllWordForms = $False
    $Forward = $True
    $Format = $False
    $wdReplaceAll = 2
    $wdFindContinue = 1

    $found = $script:Word.Selection.Find.Execute(
        $Find,
        $MatchCase,
        $MatchWholeWord,
        $MatchWildcards,
        $MatchSoundsLike,
        $MatchAllWordForms,
        $Forward,
        $wdFindContinue,
        $Format,
        $Replace,
        $wdReplaceAll
     )

    if (!$found) {
        Write-Host "WARNING: Didn't find $Find to replace with $Replace"
    }
}

# Loop over each row in the spreadsheet, skipping the header row
for ($Row = 2; $Row -le $Range.Rows.Count; $Row++) {
    Write-Host ("Processing row " + ($Row - 1).ToString() + "/" + ($Range.Rows.Count - 1).ToString())

    # We have to re-open the template file each time to get a fresh document without the replaced text
    $Doc = $Word.Documents.Open($TemplateFile, $False, $False)
    $OutFileName = $NameTemplate

    # Loop over the header columns, using them as keys for the values below
    for ($col = 1; $col -le $Header.Count; $col++) {
        $Find = "{" + $Header[$col].Text + "}"
        $Replace = ($Range.Rows[$row].Columns[$col].Text -Replace "\s+", " ").Trim()
        ReplaceText $Find $Replace

        $OutFileName = $OutFileName -ireplace [regex]::Escape($Find),$Replace
    }

    $Doc.SaveAs($OutDir + "\" + $OutFileName, $Format)
    $Doc.Close($False) # Close without saving original document
}

# Clean up after ourselves
$Word.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject( $Word ) | Out-Null
$Excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject( $Excel ) | Out-Null