Save a Workbook in Another Format with win32com

pandas and openpyxl convert data between formats, but they cannot write .xlsb or legacy .xls, and a round trip through them drops charts, macros and much of the formatting. When you need a faithful conversion — the same workbook, a different container — the most reliable converter is Excel itself. On a Windows machine with Excel installed, pywin32 lets Python open a workbook in Excel and call SaveAs with any format Excel supports, or ExportAsFixedFormat for PDF. This guide, part of Automating Excel with COM and pywin32, covers the format codes, the switches that keep Excel from stopping to ask questions, and batch conversion of whole folders.

Prerequisites

Windows, a desktop installation of Excel, and pywin32:

pip install pywin32

COM needs absolute paths — Excel's working directory is not your script's — so every example resolves paths first.

Step 1 — Open Excel safely

Start a private Excel instance, make it quiet, and guarantee it quits whatever happens. DispatchEx starts a new process rather than attaching to one the user has open, so your automation never touches their workbooks:

from pathlib import Path

import win32com.client as win32

def start_excel():
    excel = win32.DispatchEx("Excel.Application")
    excel.Visible = False
    excel.DisplayAlerts = False      # no "file exists, replace?" prompts
    excel.ScreenUpdating = False
    excel.EnableEvents = False       # do not run workbook event macros
    return excel

DisplayAlerts = False is essential for unattended runs: without it, the first SaveAs over an existing file opens a dialog nobody can answer and the script hangs. The cleanup side of this pattern, including orphaned EXCEL.EXE processes, is covered in Close Excel Cleanly and Avoid Orphan COM Processes.

Step 2 — SaveAs with a FileFormat code

Workbook.SaveAs(Filename, FileFormat) writes a copy in the format given by an XlFileFormat code. Keep the codes in one mapping keyed by extension, so the target file name decides the format:

FILE_FORMATS = {
    ".xlsx": 51,   # xlOpenXMLWorkbook
    ".xlsm": 52,   # xlOpenXMLWorkbookMacroEnabled
    ".xlsb": 50,   # xlExcel12 (binary)
    ".xls": 56,    # xlExcel8 (97-2003)
    ".csv": 62,    # xlCSVUTF8
    ".ods": 60,    # xlOpenDocumentSpreadsheet
}

def convert(src: str, dst: str) -> None:
    src_path, dst_path = Path(src).resolve(), Path(dst).resolve()
    fmt = FILE_FORMATS[dst_path.suffix.lower()]
    excel = start_excel()
    try:
        wb = excel.Workbooks.Open(str(src_path), UpdateLinks=0, ReadOnly=True)
        try:
            wb.SaveAs(str(dst_path), FileFormat=fmt)
        finally:
            wb.Close(SaveChanges=False)
    finally:
        excel.Quit()

convert("reports/q3.xlsx", "reports/q3.xlsb")

UpdateLinks=0 stops Excel from trying to refresh links to other workbooks on open, and ReadOnly=True guarantees the source is never modified. The extension and the code must agree: saving format 51 to a name ending .xls produces a file Excel later refuses to open without a warning.

Saving a macro workbook as .xlsx silently discards its VBA project — Excel would ask, but DisplayAlerts = False answers "yes, discard" for you. Choose .xlsm or .xlsb when macros must survive.

Step 3 — Export to PDF

PDF is not a SaveAs format; use ExportAsFixedFormat with type 0 (xlTypePDF) on the workbook or on a single sheet:

def to_pdf(src: str, dst: str, sheet: str | None = None) -> None:
    excel = start_excel()
    try:
        wb = excel.Workbooks.Open(str(Path(src).resolve()), ReadOnly=True)
        try:
            target = wb.Worksheets(sheet) if sheet else wb
            target.ExportAsFixedFormat(0, str(Path(dst).resolve()))
        finally:
            wb.Close(SaveChanges=False)
    finally:
        excel.Quit()

to_pdf("reports/q3.xlsx", "reports/q3_summary.pdf", sheet="Summary")

The PDF follows each sheet's page setup — print area, orientation, scaling, headers — so set those in the workbook first; see Fit an Excel Sheet to One Page Wide When Exporting to PDF.

Step 4 — Export every sheet to its own CSV

CSV holds one table, so SaveAs with a CSV code writes only the active sheet. To export them all, copy each sheet into a new single-sheet workbook and save that — calling Copy() with no arguments creates the new workbook:

def sheets_to_csv(src: str, out_dir: str) -> list[str]:
    out = Path(out_dir).resolve()
    out.mkdir(parents=True, exist_ok=True)
    written = []
    excel = start_excel()
    try:
        wb = excel.Workbooks.Open(str(Path(src).resolve()), ReadOnly=True)
        try:
            for ws in wb.Worksheets:
                ws.Copy()                              # new workbook containing only this sheet
                single = excel.ActiveWorkbook
                target = out / f"{Path(src).stem}_{ws.Name}.csv"
                single.SaveAs(str(target), FileFormat=62)
                single.Close(SaveChanges=False)
                written.append(str(target))
        finally:
            wb.Close(SaveChanges=False)
    finally:
        excel.Quit()
    return written

Code 62 writes UTF-8, which preserves accented names and symbols; the older code 6 writes in the system's ANSI code page and garbles anything outside it. Excel writes values as displayed, so a number formatted with two decimals is exported with two decimals — useful for reports, surprising for data interchange. When you need raw values, read the workbook with pandas and write the CSV from Python instead.

Step 5 — Convert a whole folder

For batch jobs, start Excel once and reuse it for every file — starting Excel takes seconds, a conversion a fraction of that. Record failures and carry on, so one broken file does not stop the batch:

def convert_folder(src_dir: str, pattern: str, suffix: str) -> dict:
    results = {"ok": [], "failed": []}
    fmt = FILE_FORMATS[suffix]
    excel = start_excel()
    try:
        for src in sorted(Path(src_dir).resolve().glob(pattern)):
            dst = src.with_suffix(suffix)
            try:
                wb = excel.Workbooks.Open(str(src), UpdateLinks=0, ReadOnly=True)
                try:
                    wb.SaveAs(str(dst), FileFormat=fmt)
                finally:
                    wb.Close(SaveChanges=False)
                results["ok"].append(src.name)
            except Exception as exc:          # pywintypes.com_error in practice
                results["failed"].append((src.name, str(exc)))
    finally:
        excel.Quit()
    return results

print(convert_folder("legacy", "*.xls", ".xlsx"))

A typical use is retiring a folder of legacy .xls files: convert once to .xlsx, and every later step can use openpyxl and pandas without the xlrd special case described in Convert .xls to .xlsx with Python.

Step 6 — Check what the conversion produced

A conversion that raised no error can still produce a surprise: a .xls target truncated at 65,536 rows, a sheet that did not survive, a CSV written in the wrong encoding. Read each result back with Python — no Excel needed — and compare it with the source:

import pandas as pd

def compare(src: str, dst: str) -> list[str]:
    engine = "calamine" if dst.endswith((".xlsb", ".xls", ".ods")) else None
    before = pd.read_excel(src, sheet_name=None)
    after = pd.read_excel(dst, sheet_name=None, engine=engine)
    problems = []
    if list(before) != list(after):
        problems.append(f"sheets differ: {list(before)} -> {list(after)}")
    for name, frame in before.items():
        if name in after and len(after[name]) != len(frame):
            problems.append(f"{name}: {len(frame)} rows -> {len(after[name])} rows")
    return problems

print(compare("reports/q3.xlsx", "reports/q3.xlsb"))   # [] when nothing was lost

The calamine engine reads .xlsb, .xls and .ods alike, so one function checks every target format. For the legacy .xls format in particular, the row check is worth keeping in the batch: Excel 97-2003 files hold at most 65,536 rows and 256 columns, and anything beyond that is cut off during conversion — with alerts disabled, silently.

Common pitfalls

• Relative paths. COM resolves paths against Excel's own current directory. Always pass absolute paths.
• Mismatched extension and code. The format code, not the extension, decides the file's content. Keep them in one mapping so they cannot drift.
• Silent macro loss. With alerts off, saving a macro workbook as .xlsx discards the macros without asking.
• Files open elsewhere. A source open in another Excel window can be read, but a target that is open elsewhere cannot be overwritten; SaveAs raises a COM error. Check the failed list.

Performance and scale notes

Excel converts a typical workbook in well under a second once it is running; startup dominates, so reuse one instance across a batch and restart it every few hundred files to release memory. Conversions run one at a time per Excel process — COM automation of Excel is single-threaded — so for very large batches run several worker processes, each with its own DispatchEx instance. Running such jobs unattended from Task Scheduler has its own requirements, covered in Run Excel COM Automation from Task Scheduler Without a Logged-In User.

Conclusion

When a conversion must be faithful, or must produce .xlsb or .xls, let Excel do it through pywin32: a private DispatchEx instance with alerts, screen updating and events off; SaveAs with the XlFileFormat code that matches the extension; ExportAsFixedFormat(0, …) for PDF; a copy-to-new-workbook step for per-sheet CSV; and one reused instance, with failures recorded, for folders. Always pass absolute paths and quit Excel in a finally block.

Frequently asked questions

What FileFormat code saves an .xlsx file? 51 (xlOpenXMLWorkbook). Other common codes are 52 for .xlsm, 50 for .xlsb, 56 for the legacy .xls format, 62 for CSV UTF-8, 6 for classic CSV and 60 for OpenDocument .ods.

Why does SaveAs to CSV only save one sheet? CSV holds a single table, so Excel saves only the active sheet. To export every sheet, copy each one into a new workbook and save that as CSV, as shown in this guide.

How do I stop Excel asking whether to overwrite a file? Set excel.DisplayAlerts = False before SaveAs. Excel then overwrites silently, so check for existing files yourself if overwriting would be a mistake.

Why use Excel for conversion when pandas can do it? Excel is the only tool that writes .xlsb and .xls, and it converts with full fidelity — formatting, charts, macros and formulas survive. pandas conversions keep data only.

Related

• Up one level: Automating Excel with COM and pywin32 — the COM basics.
• Handling Excel File Formats and Conversions — conversions without Excel.
• Convert an Excel File to PDF with Python — every route to PDF.
• Handle COM Errors and Excel Dialog Prompts in Python — when SaveAs fails.