Batch Convert Excel Files with PowerShell (And When Not To)

If you've searched for how to bulk convert .xls files, you've probably found PowerShell scripts that use the Excel COM object to open and resave files. They work — mostly. But there's a critical flaw that makes them dangerous for files with VBA macros. Here's the full picture.

How PowerShell Excel Conversion Works

PowerShell can automate Excel through the COM interface, the same way VBA can automate other Office applications. The basic pattern:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workbook = $excel.Workbooks.Open("C:\Files
eport.xls")
$workbook.SaveAs("C:\Files
eport.xlsx", 51)  # 51 = xlOpenXMLWorkbook
$workbook.Close($false)
$excel.Quit()

The format constant 51 is xlOpenXMLWorkbook — the .xlsx format. This script opens the .xls file in Excel and saves it as .xlsx.

The Critical Flaw: Format 51 Deletes Macros

Format constant 51 (.xlsx) cannot contain macros. If a .xls file has VBA code and you save it as format 51, Excel deletes all the VBA without warning. The same silent deletion that happens manually also happens through the COM interface.

The fix looks simple: use format 52 (xlOpenXMLWorkbookMacroEnabled, i.e., .xlsm) instead. But that means saving every file as .xlsm — even files that had no macros. Those files will be .xlsm unnecessarily and trigger macro warnings every time they open.

The correct approach requires detecting whether each file has macros before choosing the target format:

$workbook = $excel.Workbooks.Open($sourceFile)

# Check if workbook has VBA components
$hasMacros = $false
foreach ($component in $workbook.VBProject.VBComponents) {
    if ($component.CodeModule.CountOfLines -gt 0) {
        $hasMacros = $true
        break
    }
}

if ($hasMacros) {
    $targetFormat = 52  # .xlsm
    $targetFile = $sourceFile -replace '\.xls$', '.xlsm'
} else {
    $targetFormat = 51  # .xlsx
    $targetFile = $sourceFile -replace '\.xls$', '.xlsx'
}

$workbook.SaveAs($targetFile, $targetFormat)

Additional Problems with the PowerShell Approach

Even with macro detection, a PowerShell script doesn't handle:

• PtrSafe fixes — if VBA has Declare statements without PtrSafe, the .xlsm will still throw compile errors on 64-bit Office
• Broken references — MISSING library references aren't fixed by Save As
• ActiveX controls — 32-bit-only controls transfer to .xlsm but still fail to load
• External link updates — links to other .xls files still point to .xls even after conversion
• Password-protected files — the script fails silently or throws an error
• Excel must be installed — and the script ties up an Excel instance for the entire batch, which can take hours

When the PowerShell Approach Is Acceptable

A PowerShell script is a reasonable choice when:

• You've pre-verified that none of the files in scope have VBA macros
• You don't need PtrSafe fixes or reference cleanup
• The files are simple data workbooks without complex dependencies
• You're comfortable scripting edge case handling yourself

For a clean batch of no-macro files, the PowerShell approach is fast and free. For anything more complex, purpose-built migration tooling handles the edge cases that scripts miss.

A More Complete PowerShell Script

If you do use PowerShell, here's a more complete version that at minimum handles the macro detection correctly and logs results:

param(
    [string]$SourceFolder = "C:\Migration\Source",
    [string]$TargetFolder = "C:\Migration\Output",
    [string]$LogFile = "C:\Migration\conversion-log.csv"
)

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$log = @()
$files = Get-ChildItem $SourceFolder -Filter "*.xls" -Recurse

foreach ($file in $files) {
    $result = [PSCustomObject]@{
        Source = $file.FullName
        Target = ""
        Format = ""
        HasMacros = $false
        Status = "Pending"
        Error = ""
    }
    try {
        $wb = $excel.Workbooks.Open($file.FullName, 0, $true)  # read-only
        $hasMacros = $false
        try {
            foreach ($comp in $wb.VBProject.VBComponents) {
                if ($comp.CodeModule.CountOfLines -gt 0) { $hasMacros = $true; break }
            }
        } catch { }  # VBProject access may be restricted
        
        $result.HasMacros = $hasMacros
        $targetExt = if ($hasMacros) { ".xlsm" } else { ".xlsx" }
        $targetFmt = if ($hasMacros) { 52 } else { 51 }
        $targetPath = Join-Path $TargetFolder ($file.BaseName + $targetExt)
        
        $wb.Close($false)  # close read-only copy
        $wb = $excel.Workbooks.Open($file.FullName)  # reopen for save
        $wb.SaveAs($targetPath, $targetFmt)
        $wb.Close($false)
        
        $result.Target = $targetPath
        $result.Format = $targetExt
        $result.Status = "Success"
    } catch {
        $result.Status = "Error"
        $result.Error = $_.Exception.Message
        try { $wb.Close($false) } catch { }
    }
    $log += $result
    Write-Host "$($result.Status): $($file.Name) -> $($result.Format)"
}

$excel.Quit()
$log | Export-Csv $LogFile -NoTypeInformation
Write-Host "Done. Log written to $LogFile"

This still won't fix PtrSafe issues or broken references — but at least it won't silently delete your macros.

Related Resources

• Bulk Converting 1,000 Excel Files — Step-by-Step
• How to Audit Excel VBA Before Migration
• Why Bulk Migration Needs a Local Tool

← Back to all posts