If you're managing a fleet of macro-heavy Excel workbooks — for compliance, migration to cloud, or simply upgrading legacy tools — you've likely discovered that the standard advice ("just use online converters") doesn't work when macros are involved.
This guide covers every migration path for XLSM workbooks in 2026, what works, what doesn't, and the tools that handle it correctly.
"Excel macro migration" covers several distinct problems that need different solutions:
1. Format migration: Converting XLSM → XLSX (modern Excel, no macros) or XLSM → XLSB (binary, smaller, macros preserved) 2. Platform migration: Moving VBA workbooks from Excel to Google Sheets, LibreOffice Calc, or OnlyOffice 3. Technology migration: Converting VBA macros to modern alternatives (Power Automate, Apps Script, Python, Power Query) 4. Archive migration: Converting macro-heavy workbooks to PDF + archived VBA code for compliance/records
Each of these has different tooling, different complexity, and different risks. This guide addresses all four.
If you're migrating between Windows machines, upgrading Office versions, or consolidating from 32-bit to 64-bit Excel, you often just need the workbooks in a consistent format with macros intact.
import win32com.client
import os
from pathlib import Path
def migrate_xlsm_batch(input_dir: str, output_dir: str,
target_format: str = "xlsm") -> dict:
"""
Migrates XLSM workbooks preserving macros.
target_format: 'xlsm' (52), 'xlsb' (50), 'xls' (56)
"""
FORMAT_CODES = {
"xlsm": 52, # xlOpenXMLWorkbookMacroEnabled
"xlsb": 50, # xlExcel12 (binary, smaller)
"xls": 56, # xlExcel8 (legacy, broadest compatibility)
}
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
output_path = Path(output_dir)
output_path.mkdir(parents=True, exist_ok=True)
results = {"success": 0, "failed": 0, "errors": []}
for xlsm_file in Path(input_dir).glob("*.xlsm"):
output_file = output_path / f"{xlsm_file.stem}.{target_format}"
try:
wb = xl.Workbooks.Open(str(xlsm_file.resolve()))
wb.SaveAs(
str(output_file.resolve()),
FileFormat=FORMAT_CODES[target_format]
)
wb.Close(False)
results["success"] += 1
except Exception as e:
results["failed"] += 1
results["errors"].append(f"{xlsm_file.name}: {str(e)}")
xl.Quit()
return results
If you're migrating workbooks from 32-bit Excel to 64-bit, macros using Windows API calls will break. The fix is updating Declare statements:
' 32-bit (breaks in 64-bit Excel)
Declare Function GetWindowsDirectory Lib "kernel32" _
Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, _
ByVal nSize As Long) As Long
' 64-bit compatible (works in both 32-bit and 64-bit)
#If VBA7 Then
Declare PtrSafe Function GetWindowsDirectory Lib "kernel32" _
Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, _
ByVal nSize As Long) As Long
#Else
Declare Function GetWindowsDirectory Lib "kernel32" _
Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, _
ByVal nSize As Long) As Long
#End If
For large workbook portfolios, a macro scanner that identifies incompatible Declare statements before migration saves significant debugging time.
Google Sheets has no native VBA support. When you import an XLSM into Google Sheets, the macros are silently ignored. Your options:
Apps Script is JavaScript-based and has a similar automation model to VBA. For simple macros (loops, formulas, formatting), rewriting is usually a day's work. For complex VBA with COM calls, Windows API interaction, or custom ribbon controls, it's a full redevelopment project.
Simple VBA → Apps Script translation example:
' VBA: Format all cells in column A as currency
Sub FormatCurrency()
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lastRow).NumberFormat = "$#,##0.00"
End Sub
// Apps Script equivalent
function formatCurrency() {
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
const range = sheet.getRange(1, 1, lastRow, 1);
range.setNumberFormat('$#,##0.00');
}
For portfolios of 50+ macros, AI-assisted translation is now viable. Export each VBA module as a .bas file, then use a batch prompt to convert:
import anthropic
from pathlib import Path
client = anthropic.Anthropic()
def translate_vba_to_apps_script(vba_code: str) -> str:
response = client.messages.create(
model="claude-opus-4-6",
max_tokens=4096,
messages=[{
"role": "user",
"content": f"""Convert this VBA macro to Google Apps Script.
Preserve the exact logic. Use SpreadsheetApp API (not Charts, not Drive).
Return only the Apps Script code, no explanation.
VBA:
{vba_code}"""
}]
)
return response.content[0].text
Batch translate
for bas_file in Path("vba_exports").glob("*.bas"):
vba_code = bas_file.read_text()
apps_script = translate_vba_to_apps_script(vba_code)
output_file = Path("apps_script_output") / bas_file.with_suffix(".gs").name
output_file.write_text(apps_script)
print(f"Translated: {bas_file.name}")
Accuracy note: AI translation handles ~80-90% of standard VBA patterns correctly. COM calls, Windows API, and complex UDF patterns require manual review.
For organisations already on Microsoft 365, the modern replacement for VBA is Power Automate (workflow automation) + Power Query (data transformation).
When to migrate to Power Automate:
Before migration, export all VBA code for the record:
import win32com.client
from pathlib import Path
def export_all_vba(xlsm_path: str, output_dir: str):
"""Export all VBA modules from an XLSM workbook."""
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = False
output_path = Path(output_dir)
output_path.mkdir(parents=True, exist_ok=True)
wb = xl.Workbooks.Open(xlsm_path)
for component in wb.VBProject.VBComponents:
# Types: 1=Module, 2=Class, 3=UserForm, 100=Worksheet/Workbook
ext = {1: ".bas", 2: ".cls", 3: ".frm"}.get(component.Type, ".bas")
if component.CodeModule.CountOfLines > 0:
export_path = str(output_path / (component.Name + ext))
component.Export(export_path)
print(f"Exported: {component.Name}{ext}")
wb.Close(False)
xl.Quit()
export_all_vba(r"C:\workbooks\complex-model.xlsm", r"C:\vba-exports")
For compliance migrations (legal holds, audit archives, records management), you need both a visual record (PDF) and a code archive (exported VBA modules). The PDF represents the workbook state; the VBA archive preserves the logic for potential future audits.
import win32com.client
import os
from pathlib import Path
from datetime import datetime
def compliance_migrate(xlsm_path: str, output_dir: str, metadata: dict = None):
"""
Full compliance migration: PDF export + VBA archive + metadata.
Creates: output_dir/filename/filename.pdf, *.bas files, metadata.json
"""
import json
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
xlsm_path = Path(xlsm_path)
stem = xlsm_path.stem
output_path = Path(output_dir) / stem
output_path.mkdir(parents=True, exist_ok=True)
wb = xl.Workbooks.Open(str(xlsm_path.resolve()))
# 1. Export PDF
pdf_path = str(output_path / f"{stem}.pdf")
wb.ExportAsFixedFormat(0, pdf_path)
# 2. Export VBA modules
vba_count = 0
for component in wb.VBProject.VBComponents:
ext = {1: ".bas", 2: ".cls", 3: ".frm"}.get(component.Type, ".bas")
if component.CodeModule.CountOfLines > 0:
export_file = str(output_path / (component.Name + ext))
component.Export(export_file)
vba_count += 1
# 3. Write metadata
meta = {
"source_file": str(xlsm_path),
"migration_date": datetime.utcnow().isoformat(),
"sheet_count": wb.Worksheets.Count,
"vba_modules_exported": vba_count,
"excel_version": xl.Version,
**(metadata or {})
}
(output_path / "metadata.json").write_text(json.dumps(meta, indent=2))
wb.Close(False)
xl.Quit()
print(f"Migration complete: {stem}")
print(f" PDF: {pdf_path}")
print(f" VBA modules: {vba_count}")
return str(output_path)
| Tool | Macro Preservation | Platform | Cost | Batch Support | |------|--------------------|----------|------|---------------| | Excel + COM automation | Excellent | Windows only | Free (needs Excel license) | Yes (script it) | | Aspose.Cells | Excellent | Any | $1,200+/year | Yes (API) | | LibreOffice headless | Poor (Basic, not VBA) | Any | Free | Yes | | Online converters (all) | None (stripped) | Browser | Free-$25/mo | Limited | | Google Sheets import | None (ignored) | Browser | Free | No |
The gap: There is no mid-market tool (priced $7-50/month) that provides reliable macro-aware conversion across platforms. Enterprise users either pay for Aspose or maintain in-house COM automation scripts. This is an unsolved problem at scale.
LibreOffice shows this dialog on files with macro content. Suppress it:
libreoffice --headless --norestore --nofirststartwizard --convert-to docx file.xlsm
COM automation can unlock with:
wb.VBProject.Protection = 0 # May fail if VBA project is locked
If locked, you'll need to supply the VBA project password:
wb = xl.Workbooks.Open(path, Password="", WriteResPassword="",
ReadOnly=False, Notify=False,
AddToMru=False, MemorySize=0, Converter=0)
Install required fonts before batch conversion:
Ubuntu
sudo apt-get install ttf-mscorefonts-installer fonts-liberation
Yes. XLSX format does not support macros. Converting XLSM to XLSX permanently removes all VBA code. If you need macros preserved, use XLSM→XLSM or XLSM→XLSB as your output format.
No. Google Sheets does not support VBA. Macros are silently ignored when importing XLSM files. You must rewrite macros in Google Apps Script or Power Automate if you're migrating to Google Workspace.
XLSB (Excel Binary Workbook, FileFormat 50) is the most compact macro-enabled format and has the best read performance for large workbooks. XLSM is better for version control and diffs (it's XML-based). For archival, XLSM is preferred because the XML content can be inspected without Excel.
AI-assisted translation (Claude, GPT-4) handles 70-85% of standard VBA patterns. Complex Windows API calls, COM automation, and Excel-specific object model interactions require manual review. For production migration, plan for human review of all AI-translated code before deployment.
Every migration path described above requires either Windows + Office licences (COM automation) or $1,200+/year (Aspose). The mid-market — a web-based, macro-aware batch converter at $7-15/month — doesn't exist.
If you're a developer looking for a defensible micro-SaaS opportunity, this is it.
I researched the full competitive landscape, search demand, pricing model, and technical architecture for a macro-safe conversion product. It's packaged as a founder kit:
Macro-Safe Converter Launch Kit — keyword matrix, positioning guide, landing page copy, pricing framework, and 48-hour launch checklist. Everything needed to validate and build in this niche.
Last updated: April 2026
Macro-Safe Converter preserves VBA macros through XLSM conversions. One-time kit — no subscription.
Get the Kit — $9 one-time →