How to Pass Parameters Between Word VBA Macros and PowerShell

You need to send data from a Word VBA macro to a PowerShell script and receive results back. This is common when VBA cannot perform file operations like renaming many documents or querying system services. The challenge is that VBA and PowerShell do not share memory or variables directly. This article explains three reliable methods to pass parameters between Word VBA and PowerShell: command-line arguments, temporary text files, and the clipboard.

Why Direct Parameter Passing Does Not Work Between VBA and PowerShell

Word VBA runs inside the Word process. PowerShell runs in a separate process. They cannot call each other’s functions or share variables. VBA can start a PowerShell process using the Shell function or Shell.Run method, but it cannot read PowerShell’s output directly without extra steps. The same limitation applies in reverse: a PowerShell script cannot modify VBA variables or call VBA functions.

To pass parameters from VBA to PowerShell, you must use one of these inter-process communication methods:

• Command-line arguments: Pass data as a string when starting the PowerShell process. The script reads them from the $args array or a named parameter.
• Temporary file: VBA writes data to a text file. PowerShell reads that file, processes the data, and writes output to another file that VBA reads.
• Clipboard: VBA copies text to the clipboard. PowerShell retrieves it with Get-Clipboard. This works only for small amounts of text.

Each method has a maximum data size limit. Command-line arguments are limited to 8191 characters in Windows. The clipboard is limited by available memory but is impractical for more than a few kilobytes. A temporary file has no practical limit.

Method 1: Passing Parameters via Command-Line Arguments

This is the simplest method. You pass one or more values as arguments when starting PowerShell from VBA. The script reads them from the $args automatic array or from named parameters defined with param().

1. Create the PowerShell script
   Open Notepad and save this as C:\Scripts\ProcessDoc.ps1:
   param([string]$docPath, [string]$action)
Write-Host "Processing $docPath with action $action"
# Your processing code here
2. Write the VBA macro in Word
   Press Alt+F11 to open the VBA editor. Insert a new module and paste:
   Sub RunPowerShellWithArgs()
Dim psCmd As String
Dim docPath As String
Dim action As String
docPath = ActiveDocument.FullName
action = "ConvertToPDF"
psCmd = "powershell.exe -ExecutionPolicy Bypass -File " & Chr(34) & _
"C:\Scripts\ProcessDoc.ps1" & Chr(34) & " -docPath " & Chr(34) & _
docPath & Chr(34) & " -action " & Chr(34) & action & Chr(34)
Shell psCmd, vbHide
End Sub
3. Run the macro
   Press F5 in the VBA editor. PowerShell opens, runs the script, and exits. The script receives the document path and action as strings.

Use Chr(34) to wrap each argument in double quotes. This prevents errors when the path contains spaces. The -ExecutionPolicy Bypass flag allows the script to run even if PowerShell’s execution policy is restricted.

Method 2: Passing Parameters via a Temporary Text File

Use a temporary file when you need to pass complex data like arrays, multiple file paths, or structured data. VBA writes the data to a file in the user’s Temp folder. PowerShell reads that file, processes the data, and writes results to a second file. VBA then reads the output file.

1. Create the PowerShell script
   Save this as C:\Scripts\BatchProcess.ps1:
   param([string]$inputFile, [string]$outputFile)
$data = Get-Content $inputFile
$results = @()
foreach ($line in $data) {
$results += "Processed: $line"
}
$results | Out-File $outputFile
2. Write the VBA macro
   In the VBA editor, insert a new module and paste:
   Sub RunPowerShellWithFile()
Dim fso As Object
Dim tempFolder As String
Dim inputFile As String
Dim outputFile As String
Dim psCmd As String
Dim fileList As String
Set fso = CreateObject("Scripting.FileSystemObject")
tempFolder = fso.GetSpecialFolder(2) ' Temp folder
inputFile = tempFolder & "\vba_input.txt"
outputFile = tempFolder & "\vba_output.txt"
' Write data to input file
fileList = "C:\Docs\Report1.docx" & vbCrLf & "C:\Docs\Report2.docx"
fso.CreateTextFile(inputFile).Write fileList
' Run PowerShell
psCmd = "powershell.exe -ExecutionPolicy Bypass -File " & Chr(34) & _
"C:\Scripts\BatchProcess.ps1" & Chr(34) & _
" -inputFile " & Chr(34) & inputFile & Chr(34) & _
" -outputFile " & Chr(34) & outputFile & Chr(34)
Shell psCmd, vbHide
' Wait for script to finish
Application.Wait Now + TimeValue("0:00:02")
' Read output file
Dim result As String
result = fso.OpenTextFile(outputFile).ReadAll
MsgBox result
End Sub
3. Run the macro
   Press F5. The macro writes the file list to vba_input.txt, runs PowerShell, waits two seconds, and displays the results from vba_output.txt.

The Application.Wait line gives PowerShell time to finish. For longer scripts, use a loop that checks if the output file exists before reading it.

Method 3: Passing Parameters via the Clipboard

Use the clipboard for a single small value such as a document name or a folder path. VBA copies the text to the clipboard. PowerShell retrieves it with Get-Clipboard. This method does not create temporary files.

1. Create the PowerShell script
   Save this as C:\Scripts\ClipboardProcess.ps1:
   $clipText = Get-Clipboard
Write-Host "Clipboard contains: $clipText"
# Your processing code here
2. Write the VBA macro
   In the VBA editor, insert a new module and paste:
   Sub RunPowerShellWithClipboard()
Dim psCmd As String
' Copy document path to clipboard
With New DataObject
.SetText ActiveDocument.FullName
.PutInClipboard
End With
psCmd = "powershell.exe -ExecutionPolicy Bypass -File " & Chr(34) & _
"C:\Scripts\ClipboardProcess.ps1" & Chr(34)
Shell psCmd, vbHide
End Sub
3. Enable the Microsoft Forms 2.0 Object Library
   In the VBA editor, click Tools > References. Check Microsoft Forms 2.0 Object Library. This adds the DataObject class used for clipboard access.

The clipboard method overwrites whatever the user has in the clipboard. Use it only when you control the entire workflow and the user is not copying other data.

Common Issues When Passing Parameters Between VBA and PowerShell

PowerShell script does not run or shows no output

The most common cause is PowerShell’s execution policy. By default, Windows blocks script execution. Always include -ExecutionPolicy Bypass in the command line. If the script still does not run, open PowerShell as administrator and run Set-ExecutionPolicy RemoteSigned to allow local scripts permanently.

Spaces in file paths break the command

When a file path contains spaces, PowerShell interprets the path as multiple arguments. Wrap every path argument in double quotes using Chr(34) in VBA. For example: """" & docPath & """" produces a quoted string.

VBA macro finishes before PowerShell completes

The Shell function runs PowerShell asynchronously. VBA continues to the next line immediately. To wait for PowerShell to finish, use the WScript.Shell object’s Run method with the wait parameter set to True: CreateObject("WScript.Shell").Run psCmd, 0, True. The third parameter True makes VBA wait until the process exits.

PowerShell script cannot access Word objects

PowerShell runs in a separate process. It cannot call Word’s object model directly unless you use the Word COM object within PowerShell. If you need PowerShell to interact with Word, add this to the script: $word = New-Object -ComObject Word.Application; $doc = $word.Documents.Open($docPath). Pass the document path as a parameter as described in Method 1.

Item	Command-Line Arguments	Temporary Text File	Clipboard
Data limit	8191 characters total	No practical limit	Limited by memory, impractical over 10 KB
Speed	Fastest, no disk I/O	Slower due to file read/write	Fast, no disk I/O
Complex data support	Simple strings only	Arrays, structured text, JSON	Single text string
User clipboard overwritten	No	No	Yes
Best use case	One or two short string parameters	Multiple file paths, large data, or results needed back in VBA	Quick single-value transfer in a controlled workflow

You can now pass parameters from Word VBA to PowerShell using command-line arguments, temporary files, or the clipboard. Each method serves a different data size and complexity requirement. Start with command-line arguments for simple tasks. Switch to a temporary file when you need to exchange arrays or large data sets. Use the clipboard only for small, single-value transfers in workflows where you control the clipboard content. To extend this further, consider writing the PowerShell output to an XML file and parsing it in VBA with the MSXML2.DOMDocument object for structured data exchange.