SteadyLatch

Guides · Excel automation

Run an Excel Macro from PowerShell with a Timeout

Published · Tested with Microsoft 365 Excel 16.0.20430.20092 (64-bit), PowerShell 7.6.6, Windows 11 Pro

Calling a macro from PowerShell takes three lines. Calling it so that a dialog, an unhandled VBA error or a stuck Excel does not leave your script waiting forever takes a little more. This guide builds that: two short scripts, a synthetic workbook, and the four outcomes you will see.

Before you start

Why a macro run "hangs"

When we tested the naive version (open, Run, save, quit), three things stopped it, and none of them was a slow macro:

  1. An unhandled VBA error. With Excel hidden, a runtime error still opens the VBA error dialog. Nobody clicks it, so Run never returns.
  2. A MsgBox or InputBox. DisplayAlerts = $false silences Excel's own prompts (save changes, overwrite file), not the macro's dialogs. It does not apply to security warnings either (Microsoft Learn).
  3. An Excel that stays behind. Quit() is a request. While a COM reference is still alive, EXCEL.EXE keeps running without a window; in our tests it sometimes outlived the script that started it. Over a day of scheduled runs, those add up.

So the design has two parts: the macro reports its own errors as text, and the waiting happens in a process that Excel cannot block.

The example workbook

Create report.xlsm with two sheets, Input (columns Date, Store, Units, Amount, with a few rows of invented data) and an empty Report. Add a standard module named Report with this code:

Option Explicit

' Builds a per-store summary of the Input sheet on the Report sheet.
' Input columns: A Date, B Store, C Units, D Amount (row 1 is the header).
Public Sub BuildReport()
    Dim src As Worksheet, dst As Worksheet
    Set src = ThisWorkbook.Worksheets("Input")
    Set dst = ThisWorkbook.Worksheets("Report")
    dst.Cells.Clear
    dst.Range("A1:C1").Value = Array("Store", "Units", "Amount")

    Dim totals As Object
    Set totals = CreateObject("Scripting.Dictionary")
    Dim lastRow As Long, r As Long, store As String, t As Variant
    lastRow = src.Cells(src.Rows.Count, 1).End(xlUp).Row
    For r = 2 To lastRow
        store = CStr(src.Cells(r, 2).Value)
        If Not totals.Exists(store) Then totals.Add store, Array(0#, 0#)
        t = totals(store)
        t(0) = t(0) + src.Cells(r, 3).Value
        t(1) = t(1) + src.Cells(r, 4).Value
        totals(store) = t
    Next r

    Dim i As Long, k As Variant
    i = 2
    For Each k In totals.Keys
        dst.Cells(i, 1).Value = k
        dst.Cells(i, 2).Value = totals(k)(0)
        dst.Cells(i, 3).Value = Round(totals(k)(1), 2)
        i = i + 1
    Next k
End Sub

' Entry point for unattended runs: returns "OK" when the report was built,
' or the error as text. An unhandled error would open the VBA error dialog
' and wait for a click that never comes.
Public Function BuildReportSafely() As String
    On Error GoTo Failed
    BuildReport
    BuildReportSafely = "OK"
    Exit Function
Failed:
    BuildReportSafely = "Error " & Err.Number & " in BuildReport: " & Err.Description
End Function

' A macro that waits for a person, to test the timeout.
Public Sub WaitForUser()
    MsgBox "Check the totals, then click OK."
End Sub

BuildReportSafely is the entry point for unattended runs. It calls the real macro and turns any error into a return value, so no error dialog opens. The convention is simple: "OK" means it worked, anything else is the error message. WaitForUser is there only to show what a dialog does to a run.

The worker: one Excel, one macro

The worker starts its own Excel, writes that Excel's process id to a file, runs the macro, saves and writes a status file. It never decides how long to wait; that is the controller's job.

# macro-worker.ps1: opens one workbook in its own Excel, runs one macro, saves, quits.
# Started by run-macro.ps1; never run it on your only copy of a workbook.
param(
    [Parameter(Mandatory)] [string] $Workbook,
    [Parameter(Mandatory)] [string] $Macro,
    [Parameter(Mandatory)] [string] $PidFile,
    [Parameter(Mandatory)] [string] $StatusFile
)
$ErrorActionPreference = 'Stop'

Add-Type -Namespace Win32Tools -Name User32 -MemberDefinition @'
[DllImport("user32.dll")]
public static extern uint GetWindowThreadProcessId(System.IntPtr hWnd, out uint processId);
'@

function Write-Status([string] $Status, [string] $Message) {
    [ordered]@{ status = $Status; message = $Message } | ConvertTo-Json |
        Set-Content -LiteralPath $StatusFile -Encoding utf8
}

$excel = $null
$book = $null
try {
    try { $excel = New-Object -ComObject Excel.Application }
    catch { Write-Status 'no-excel' $_.Exception.Message; exit 3 }

    # Record the process id of *this* Excel, so the controller can stop it and nothing else.
    $excelPid = [uint32] 0
    [void] [Win32Tools.User32]::GetWindowThreadProcessId([IntPtr] $excel.Hwnd, [ref] $excelPid)
    Set-Content -LiteralPath $PidFile -Value $excelPid

    $excel.Visible = $false
    $excel.DisplayAlerts = $false   # does not cover security warnings or MsgBox calls
    $book = $excel.Workbooks.Open($Workbook)
    try {
        $result = $excel.Run("'$($book.Name)'!$Macro")
    }
    catch {
        Write-Status 'macro-error' $_.Exception.Message
        exit 1
    }
    # Convention: a macro written as a Function returns "OK" when it worked, or the error text.
    if ($result -is [string] -and $result -ne 'OK') {
        Write-Status 'macro-error' $result
        exit 1
    }
    $book.Save()
    Write-Status 'ok' "Ran $Macro"
    exit 0
}
finally {
    if ($book) { $book.Close($false); [void] [Runtime.InteropServices.Marshal]::ReleaseComObject($book) }
    if ($excel) { $excel.Quit(); [void] [Runtime.InteropServices.Marshal]::ReleaseComObject($excel) }
}

Two details matter. GetWindowThreadProcessId turns Excel's window handle into a process id, so the controller can later stop this Excel and no other. And a function that returns something other than "OK" is treated as a failure, so an error the macro caught still fails the run.

The controller: copy, wait, clean up

# run-macro.ps1: runs one macro on a copy of a workbook, with a time limit.
# Exit codes: 0 ok, 1 the macro failed, 2 timed out, 3 Excel not available.
param(
    [Parameter(Mandatory)] [string] $Workbook,
    [Parameter(Mandatory)] [string] $Macro,
    [Parameter(Mandatory)] [string] $OutputPath,
    [int] $TimeoutSeconds = 120
)
$ErrorActionPreference = 'Stop'

# 1. Work on a copy in a new folder: the original is never opened.
$work = Join-Path ([IO.Path]::GetTempPath()) ('macro-run-' + [guid]::NewGuid().ToString('N'))
New-Item -ItemType Directory -Path $work | Out-Null
$copy = Join-Path $work (Split-Path -Leaf $Workbook)
Copy-Item -LiteralPath $Workbook -Destination $copy
$pidFile = Join-Path $work 'excel.pid'
$statusFile = Join-Path $work 'status.json'

# 2. Start the worker as its own process: if Excel blocks, this script does not.
$start = [Diagnostics.ProcessStartInfo]::new((Get-Process -Id $PID).Path)
foreach ($arg in '-NoProfile', '-NonInteractive', '-File', (Join-Path $PSScriptRoot 'macro-worker.ps1'),
    '-Workbook', $copy, '-Macro', $Macro, '-PidFile', $pidFile, '-StatusFile', $statusFile) {
    $start.ArgumentList.Add($arg)
}
$start.UseShellExecute = $false
$worker = [Diagnostics.Process]::Start($start)

# 3. Wait for the worker's status file, not for the process: trust what it reports.
$deadline = (Get-Date).AddSeconds($TimeoutSeconds)
while (-not (Test-Path -LiteralPath $statusFile) -and -not $worker.HasExited -and (Get-Date) -lt $deadline) {
    Start-Sleep -Milliseconds 250
}
Start-Sleep -Milliseconds 500   # let the worker finish writing and close the workbook
$status = if (Test-Path -LiteralPath $statusFile) { Get-Content -LiteralPath $statusFile -Raw | ConvertFrom-Json }
$timedOut = -not $status -and -not $worker.HasExited

# 4. Clean up: the worker, then the Excel this run started. Never Get-Process EXCEL | Stop-Process.
if (-not $worker.WaitForExit(10000)) { $worker.Kill($true) }
$excelPid = if (Test-Path -LiteralPath $pidFile) { [int] (Get-Content -LiteralPath $pidFile) }
$excel = if ($excelPid) { Get-Process -Id $excelPid -ErrorAction SilentlyContinue }
if ($excel -and -not $excel.WaitForExit(5000)) { $excel.Kill(); [void] $excel.WaitForExit(5000) }

# 5. Keep the output only when the worker says everything worked.
switch ($status.status) {
    'ok' {
        Copy-Item -LiteralPath $copy -Destination $OutputPath -Force
        Remove-Item -LiteralPath $work -Recurse -Force
        Write-Output "OK: $Macro ran; output saved to $OutputPath"
        exit 0
    }
    'macro-error' { Write-Output "MACRO FAILED: $($status.message). Output not saved."; exit 1 }
    'no-excel' { Write-Output "EXCEL NOT AVAILABLE: $($status.message)"; exit 3 }
    default {
        if (-not $timedOut) { Write-Output 'FAILED: the worker stopped without a status. Output not saved.'; exit 1 }
        Write-Output "TIMEOUT: $Macro did not finish in $TimeoutSeconds s (a dialog or a loop?). Output not saved."
        exit 2
    }
}

Try the four outcomes

Run these from the folder with the two scripts. The output below is copied from our test runs.

It works.

PS> ./run-macro.ps1 -Workbook report.xlsm -Macro Report.BuildReportSafely -OutputPath out.xlsm
OK: Report.BuildReportSafely ran; output saved to out.xlsm

The macro fails and says why. In a copy where the Input sheet was renamed:

PS> ./run-macro.ps1 -Workbook report-renamed.xlsm -Macro Report.BuildReportSafely -OutputPath out.xlsm
MACRO FAILED: Error 9 in BuildReport: Subscript out of range. Output not saved.

The same error, unhandled. Calling BuildReport directly on that copy opens the VBA error dialog, so the run ends at the time limit:

PS> ./run-macro.ps1 -Workbook report-renamed.xlsm -Macro Report.BuildReport -OutputPath out.xlsm -TimeoutSeconds 30
TIMEOUT: Report.BuildReport did not finish in 30 s (a dialog or a loop?). Output not saved.

A dialog waits for a click.

PS> ./run-macro.ps1 -Workbook report.xlsm -Macro Report.WaitForUser -OutputPath out.xlsm -TimeoutSeconds 30
TIMEOUT: Report.WaitForUser did not finish in 30 s (a dialog or a loop?). Output not saved.

After each run, Get-Process EXCEL showed no Excel left from the test. The exit codes let a calling script react without reading the text:

0
The macro ran and the output was saved.
1
The macro reported an error, or the worker stopped without a status.
2
Time limit reached: a dialog, a loop or a stuck Excel. Run the workbook by hand to see which.
3
Excel could not be started.

Scheduling, CI and what this does not do

Just want the scripts? The Scripts Pack · €12 has the code of this guide and the other guides ready to run, with examples and tests. The code on this page stays free.

The code on this page was written for this guide and tested on the versions above. The workbook and its data are invented.