Before you migrate a library of .xls files to .xlsx, you need to know which ones contain VBA macros. The answer changes everything: macro-free files can be converted quickly with basic tools, but macro-heavy files require specialized handling or you risk losing years of automation work. Here are four reliable methods — from the fastest one-second check to a PowerShell audit of your entire library.
The .xlsx format is a macro-free zone by design. When Excel saves an .xls file with macros as .xlsx, it silently strips the VBA code. You get a warning dialog, but if you click through it (which everyone does), the macros are gone from the output file.
Files with VBA must be saved as .xlsm (Excel Macro-Enabled Workbook) to preserve the code. That means your migration plan needs to distinguish between two tiers before you start: files that can become .xlsx, and files that must become .xlsm.
Don't rely on file size as a proxy. A small .xls file can contain dozens of VBA modules. A large .xls file full of data might have zero. You need to actually check.
File extensions give you a partial answer in under a second:
| Extension | What It Means |
|---|---|
.xlsx |
Definitely no macros (format prohibits them) |
.xlsm |
Definitely has a VBA project (may or may not have active code) |
.xlsb |
Binary format — may contain macros, requires opening to check |
.xls |
May or may not contain macros — need to check further |
.xla / .xlam |
Add-in files — almost certainly contain VBA |
The limitation: .xls files are the legacy format used from Excel 97 through Excel 2003. They can contain macros or not — the extension doesn't tell you. Since most legacy files you'll be migrating are .xls, you need the methods below.
The fastest way to check a single file:
If you see any of the following, the file contains VBA:
A "clean" VBA editor shows only the workbook and sheet objects with no code — no modules, no forms, no class modules.
Sheet-level event handlers are easy to miss. Code attached directly to a worksheet (like Worksheet_Change or Workbook_Open) appears under the sheet icons in the Project Explorer, not as a standalone module. Check every sheet entry by double-clicking it.
A faster visual check for named macros:
The limitation: the Macro dialog only shows Sub procedures that are publicly accessible and take no arguments. It won't show:
So a clean Macro dialog does not guarantee a clean file. Always pair this with the VBA Editor check if you need to be certain.
For a library of 10, 100, or 1,000 files, you need automation. This PowerShell script opens each Excel file, checks for VBA components, and outputs a CSV report.
# Audit a folder of Excel files for VBA macros
# Run in PowerShell as Administrator with Excel installed
$folder = "C:\Files\LegacyExcel" # Change to your folder
$output = "C:\Files\vba-audit.csv"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$results = @()
Get-ChildItem -Path $folder -Include "*.xls","*.xlsm","*.xlsb" -Recurse | ForEach-Object {
$file = $_.FullName
$hasVba = $false
$components = @()
try {
$wb = $excel.Workbooks.Open($file, 0, $true) # Read-only
if ($wb.HasVBProject) {
$vbp = $wb.VBProject
foreach ($component in $vbp.VBComponents) {
$lineCount = $component.CodeModule.CountOfLines
if ($lineCount -gt 0) {
$hasVba = $true
$components += "$($component.Name) ($lineCount lines)"
}
}
}
$wb.Close($false)
}
catch {
$components = @("ERROR: $($_.Exception.Message)")
}
$results += [PSCustomObject]@{
File = $file
HasVBA = $hasVba
Components = ($components -join "; ")
}
}
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
$results | Export-Csv -Path $output -NoTypeInformation
Write-Host "Audit complete. Results saved to $output"
Write-Host "Files with VBA: $(($results | Where-Object HasVBA).Count) of $($results.Count)"This script outputs a CSV with three columns: file path, whether VBA was found, and a list of VBA components with their line counts. You can open it in Excel to sort and filter — highest line counts first, for example, to identify your most complex files.
Note: This script requires Excel to be installed on the machine running it, and it opens each file (read-only). On large libraries, this takes time — roughly 2–5 seconds per file. For 500 files, plan for 20–40 minutes.
The free LegacyLeaps scanner audits your entire file library, classifies each file by complexity, and shows exactly which files contain VBA, ActiveX controls, or external references — without writing a line of PowerShell.
Try the Free ScanFor .xlsm files specifically, you can check for VBA without opening Excel at all. .xlsm files are ZIP archives — rename to .zip and open with 7-Zip or Windows Explorer to look inside.
Look for the file xl/vbaProject.bin in the archive. If it exists, the file contains a VBA project. If the xl/ folder exists but vbaProject.bin is absent, the file has no VBA.
This approach doesn't work on .xls files — the legacy binary format requires Excel (or a specialized library) to parse.
Once you have your audit, categorize your files into three groups:
| Category | Contains | Migration Path |
|---|---|---|
| Data-only | No VBA, no ActiveX | Convert to .xlsx — fast and safe |
| Macro-enabled | VBA modules, event handlers | Convert to .xlsm — requires VBA audit for 64-bit compatibility |
| Complex | ActiveX controls, external references, UserForms | Convert to .xlsm — consider done-for-you service for business-critical files |
The macro-enabled and complex categories need more than a format conversion. VBA code that was written for 32-bit Office (common in .xls files from the 2000s) may need PtrSafe declarations added to run correctly on 64-bit Office. ActiveX controls need to be re-registered. External file references need to be re-pointed.
Some older files contain a VBA project with zero lines of active code — Excel created the VBA container but no one ever wrote anything in it. These files will show HasVBProject = True in your audit but contain no functional macros. You can safely convert them to .xlsx. The PowerShell script above handles this correctly by checking CountOfLines, not just whether a VBA project exists.
Practical fixes for legacy Excel and Access problems. No spam.