Pillar Guide

The Complete Guide to Legacy Excel Migration

Millions of businesses still run on .xls files built in Excel 97, 2000, 2002, and 2003. These files contain years — sometimes decades — of critical business logic: formulas built over quarterly cycles, VBA macros that automate entire workflows, ActiveX forms that your team uses every day without thinking twice about them.

And then Windows updates. Office updates. And files that worked yesterday don't work today.

This guide covers everything you need to know to migrate legacy .xls files to modern formats — safely, completely, without losing your macros or breaking your formulas. Whether you're migrating one file or five hundred, this is the definitive reference.

Understanding the Formats

Before migrating anything, understand exactly what you're working with and what you're migrating to.

Format	Full Name	Era	Max Rows	Contains Macros
.xls	Excel 97-2003 Workbook	1997–2003	65,536	Yes (BIFF8)
.xlsx	Excel Workbook	2007+	1,048,576	No
.xlsm	Excel Macro-Enabled Workbook	2007+	1,048,576	Yes
.xlsb	Excel Binary Workbook	2007+	1,048,576	Yes

Which Format Should You Migrate To?

• .xls with VBA macros → .xlsm. The macro-enabled format is the direct modern equivalent. Your code transfers intact.
• .xls without macros → .xlsx. The standard modern format. Smaller files, better compatibility, full feature support.
• Very large files with macros → .xlsb. The binary format loads faster for large files and supports macros. Less human-readable but excellent for performance.

Why Migration Can't Wait

The .xls format was Microsoft's primary spreadsheet format for a decade. Microsoft stopped developing it in 2007 and has been progressively removing support ever since. The pattern is consistent and predictable:

• Each Windows update tightens security restrictions around legacy formats
• Each Office update reduces tolerance for old-style VBA patterns
• Windows 11 24H2 removed the Jet 4.0 OLE DB provider entirely and expanded MOTW blocking
• Future updates will continue this trend — Microsoft has never reversed course on legacy format deprecation

The question isn't whether your .xls files will eventually break. It's whether they'll break during a planned, controlled migration you initiate — or during an unplanned crisis that catches you off guard at the worst possible moment.

Pre-Migration Audit: What to Assess Before You Start

A migration done without a prior audit is a migration done blind. Before touching any files, know what's inside them.

For Each File, Document:

• File size and age — larger files and older files carry more risk
• Presence of VBA — press Alt + F11; any modules, class modules, forms, or code behind sheets means this file needs .xlsm
• Win32 API declarations — search for Declare Function or Declare Sub in the VBA editor; these need PtrSafe updates
• ActiveX controls — look on each sheet for command buttons, combo boxes, text boxes, and list boxes created from the Developer → Insert → ActiveX Controls section
• External references — any formulas with =[Other.xls] syntax or data connections that link to other files
• Named ranges — check Formulas → Name Manager; note workbook-scoped vs sheet-scoped names
• Business criticality — which files are actively used and by whom? Prioritize accordingly

Categorize Your Files

Group files into three categories based on your audit:

• Simple: Data only, no macros, no ActiveX controls. These convert to .xlsx with minimal risk.
• Standard: Has VBA macros, possibly some ActiveX controls, no complex external dependencies. Convert to .xlsm with standard process.
• Complex: Extensive VBA, Win32 API calls, inter-file dependencies, ActiveX forms, or external data connections. These need careful, tested migration.

The Migration Process, Step by Step

This process applies to a single file. For batch migration, see the Batch Migration section below.

Step 1: Back Up First

Copy the original .xls file to a backup location before doing anything. Keep the backup until you've fully validated the migrated file in production. Do not delete the original until at least 4 weeks of successful use of the new format.

Step 2: Open and Inspect

Open the .xls file in your current version of Excel. If it opens with warnings, note them. If macros are blocked, don't unblock yet — just note the state.

Step 3: Audit the VBA (if applicable)

Open the VBA editor (Alt + F11). Check:

1. Tools → References: any "MISSING:" references need resolving
2. Each module for Declare Function/Declare Sub statements needing PtrSafe
3. Code behind each sheet and ThisWorkbook for event handlers

Step 4: Save As the Correct Format

• If the file has VBA: File → Save As → Excel Macro-Enabled Workbook (*.xlsm)
• If the file has no VBA: File → Save As → Excel Workbook (*.xlsx)

Save to a new filename (e.g., report_2026.xlsm) rather than overwriting the original.

Step 5: Fix VBA Issues in the New File

Working from the newly saved .xlsm file:

• Update broken references (Tools → References)
• Add PtrSafe to all Win32 API declarations
• Test each macro by running it through the expected workflow

Step 6: Update External References

If your file links to other .xls files, those links now point to old .xls paths. After migrating all inter-related files, use Edit → Links (or Data → Queries & Connections → Edit Links) to update each link to point to the new .xlsm or .xlsx version.

Step 7: Validate

See the full validation checklist in the Post-Migration Validation section below.

Preserving VBA Macros

VBA macros are the most complex part of any .xls migration. They survive the conversion to .xlsm format, but they may need code changes to work correctly in modern 64-bit Office.

The PtrSafe Update

Any Declare Function or Declare Sub statement that calls a Windows DLL must be updated. The pattern:

' Old — fails on 64-bit Office:
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" _
    (ByVal hwnd As Long, ByVal lpText As String, _
     ByVal lpCaption As String, ByVal wType As Long) As Long

' Updated — works on both 32-bit and 64-bit:
#If VBA7 Then
Declare PtrSafe Function MessageBox Lib "user32" Alias "MessageBoxA" _
    (ByVal hwnd As LongPtr, ByVal lpText As String, _
     ByVal lpCaption As String, ByVal wType As Long) As Long
#Else
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" _
    (ByVal hwnd As Long, ByVal lpText As String, _
     ByVal lpCaption As String, ByVal wType As Long) As Long
#End If

The key changes: PtrSafe keyword added, and hwnd As Long changed to hwnd As LongPtr because window handles are pointer-sized values.

For a detailed reference of common API declarations and their updated forms, see our guide on fixing PtrSafe errors after a Windows update.

Updating Deprecated Methods and Properties

Some VBA methods and properties that worked in Excel 2003 produce errors or unexpected behavior in modern Excel. Common issues:

• Application.Dialogs(xlDialogOpen).Show — behavior changed in newer Excel versions; use Application.GetOpenFilename instead
• ActiveCell.FormulaR1C1 patterns — still work but may behave differently with new calculation engine
• Early binding to Office objects (e.g., Dim wb As Excel.Workbook) — update the reference version if the library version changed

Testing Macro Coverage

Don't just test the "happy path." Test edge cases and error conditions. Many VBA bugs that surface after migration only appear when:

• The macro runs on an empty dataset
• A referenced sheet or named range doesn't exist
• The user cancels a dialog the macro opens
• The file is opened from a network path rather than a local drive

Handling ActiveX Controls

ActiveX controls are the interactive elements placed directly on worksheet surfaces — command buttons, combo boxes, list boxes, text boxes, option buttons, and checkboxes created from Developer → Insert → ActiveX Controls (not Form Controls).

These controls persist in .xlsm files and generally migrate cleanly. However, they're subject to ActiveX security policies and can be blocked if the file isn't in a Trusted Location. After migration:

1. Open the .xlsm file and confirm Design Mode (Developer → Design Mode) can be toggled
2. Click each ActiveX control to confirm it responds
3. Enter Design Mode, right-click each control → View Code to confirm the event handler exists
4. Test each control's intended function

If controls render as grey boxes or fail to respond, the file needs to be in a Trusted Location. Add the folder to Excel's Trusted Locations (File → Options → Trust Center → Trust Center Settings → Trusted Locations).

Troubleshooting Broken Formulas and Functions

Most formulas migrate from .xls to .xlsx/.xlsm without issues. The exceptions are worth knowing.

Legacy Function Names

Excel 2010 renamed several statistical and engineering functions for consistency. The old names still work as compatibility functions, but they may display with a compatibility warning triangle. If your formulas show these warnings after migration, they're technically fine but can be updated:

Old Name (still works)	New Name (preferred)
STDEV	STDEV.S
STDEVP	STDEV.P
VAR	VAR.S
RANK	RANK.EQ
PERCENTILE	PERCENTILE.INC
MODE	MODE.SNGL

Array Formula Behavior

Legacy array formulas (entered with Ctrl + Shift + Enter in Excel 2003) appear with curly braces: {=SUM(IF(A1:A10>0,A1:A10))}. These work in modern Excel but are the old syntax. Modern Excel supports dynamic arrays that don't require Ctrl + Shift + Enter — if you're doing significant work on the formulas anyway, consider modernizing them.

Circular Reference Behavior

Modern Excel's iterative calculation settings may differ from Excel 2003 defaults. If circular references that worked before now produce errors, check: File → Options → Formulas → Enable iterative calculation. Verify the "Maximum Iterations" and "Maximum Change" values match what the file expects.

Formulas That Reference .xls Files by Name

If your formulas contain references like =[OtherFile.xls]Sheet1!A1, those references break when OtherFile.xls is migrated to OtherFile.xlsx. Update all cross-file references after migrating all dependent files. Use Find & Replace (Ctrl + H) to bulk-replace .xls] with .xlsx] or .xlsm] across the workbook.

Data Connections and External Queries

Legacy data connections using ODBC or OLE DB with the Jet 4.0 provider will fail on Windows 11 24H2. To fix, edit the connection string in Data → Queries & Connections → Properties and update the provider to Microsoft.ACE.OLEDB.12.0.

Batch Migration Strategies

Migrating a handful of files manually is feasible. Migrating dozens or hundreds requires a systematic approach.

Inventory First

Before running any batch conversion, build a complete inventory of all .xls files on your network. Use PowerShell to find them:

Get-ChildItem -Path "\\server\share" -Filter "*.xls" -Recurse |
Select-Object FullName, Length, LastWriteTime |
Export-Csv -Path "xls-inventory.csv" -NoTypeInformation

Sort by LastWriteTime descending to find the most recently modified files — these are likely still actively used and are your highest priority.

Prioritize by Risk and Activity

Not all files need immediate migration. Prioritize:

1. Highest priority: Files modified in the last 6 months, used by multiple people, or involved in critical business processes
2. Medium priority: Files modified in the last 2 years, used occasionally
3. Lower priority: Archived files not modified in 2+ years (migrate eventually, not urgently)

Batch Conversion with PowerShell

For simple files (no complex VBA), PowerShell via Excel COM automation handles batch conversion:

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

Get-ChildItem -Path "C:\Files" -Filter "*.xls" | ForEach-Object {
    $wb = $excel.Workbooks.Open($_.FullName)
    $hasMacros = $wb.HasVBProject
    $ext = if ($hasMacros) { ".xlsm" } else { ".xlsx" }
    $format = if ($hasMacros) { 52 } else { 51 }  # 52=xlsm, 51=xlsx
    $newPath = [System.IO.Path]::ChangeExtension($_.FullName, $ext)
    $wb.SaveAs($newPath, $format)
    $wb.Close($false)
    Write-Host "Converted: $($_.Name)"
}
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null

This script detects whether each file has a VBA project and saves to .xlsm or .xlsx accordingly. It does not fix PtrSafe errors or update broken references — that still requires per-file attention for complex files.

Batch Conversion: PowerShell vs LegacyLeaps

The critical thing to understand about PowerShell batch conversion is the macro trap. The xlOpenXMLWorkbook format constant (51) saves as .xlsx — which silently strips all VBA code. To preserve macros you need format constant 52 (.xlsm), but that requires knowing in advance which files have macros. Without a pre-scan, you're guessing.

The improved PowerShell script above checks $wb.HasVBProject and saves to the correct format — but it still can't fix PtrSafe errors, update broken references, or handle ActiveX controls. For those, you need per-file attention.

When PowerShell is the right choice: confirmed macro-free files, small batches (under 200), or as the second step after LegacyLeaps identifies and handles the macro-bearing files.

When LegacyLeaps is the right choice: unknown macro content, any files with VBA or ActiveX controls that must be preserved, large batches under time pressure, or any situation where you need an audit trail of what was converted and why.

Using LegacyLeaps for Batch Migration

LegacyLeaps handles batch migration with automatic PtrSafe fixes, ActiveX auditing, and a compatibility report per file. Point it at a folder, configure your output settings, and let it process the queue. The report at the end shows every issue found and every change made — across all files in the batch. For a 1,000-file batch that would take 45-90 minutes with PowerShell + Excel COM, LegacyLeaps typically completes in 5-10 minutes using parallel native conversion.

Post-Migration Validation

Migration isn't complete until you've validated that the converted file works correctly. Use this checklist for every migrated file:

Data Integrity

• Row and column counts match the original
• Spot-check values in key cells against the original
• Named ranges exist and point to the correct cells
• Data validation rules are present on the correct cells

Formulas

• Key calculated cells produce the same results as in the original
• No formula errors (#REF!, #NAME!, #N/A) that weren't present before
• Cross-sheet and cross-workbook references resolve correctly
• Pivot tables refresh without errors

VBA (if applicable)

• VBA editor shows all expected modules, class modules, and forms
• No "MISSING:" references in Tools → References
• Each macro runs to completion without errors
• Event handlers fire on the expected triggers (opening file, changing cells, button clicks)
• Forms open and behave correctly

Formatting and Visual

• Charts render correctly with correct data series
• Conditional formatting rules are applied
• Merged cells, column widths, and row heights match
• Headers, footers, and print settings preserved

Security and Access

• Sheet passwords (if any) still work
• VBA project password (if any) still works
• File can be opened without errors from a network share (if that's how it's normally accessed)

Frequently Asked Questions

How long does migration actually take?

A simple .xls with no macros: 2 minutes. A complex workbook with extensive VBA, Win32 API calls, and ActiveX forms: 2–4 hours including testing. For a batch of 50 files with average complexity, plan for 1–2 days of focused work with manual validation, or a few hours with LegacyLeaps handling the conversion and audit.

Should I migrate files that are still working fine?

Yes. "Working fine now" doesn't mean "safe from the next update." The whole point of a planned migration is that you choose the timing, not a Windows update. Migrate on your schedule, with proper testing, before something breaks in production.

Can I roll back if something goes wrong?

Yes, if you kept the original .xls files. This is why Step 1 of the migration process is backing up originals. Don't delete .xls files until the migrated versions have been running in production for at least 4 weeks.

My file has password-protected sheets. Does migration affect that?

Sheet protection carries over to .xlsm format. The passwords remain the same. However, if you've lost the password to an .xls workbook, migration is a good time to address that — recovery tools exist for both formats, but it's easier to deal with before migration than after.

What happens to .xlsb files — should I migrate those too?

The .xlsb format is a modern binary format (Excel 2007+) and doesn't face the same compatibility issues as .xls. It supports macros. If you have .xlsb files, they don't need migration for format reasons — they're already in a modern format.

Will migration affect how my file looks on a Mac?

Formatting, formulas, and data are cross-platform in .xlsx and .xlsm. However, VBA macros and ActiveX controls are Windows-only. If your workbook needs to run on Mac, macros will not execute on macOS Excel — that's not a migration issue, it's a platform limitation that exists in both .xls and .xlsm.

← Back to all posts