Macros and Why Data Teams Should Block Them by Default

A macro is a program stored inside a workbook. When someone opens the file and clicks Enable Content, that program runs with the privileges of the person who clicked: it can read their files, send email through their account and call whatever the network allows. For a team that moves data between spreadsheets and databases, macros are the one feature of Excel that adds risk without adding anything the pipeline needs. This article gives the format facts, the policy and the cases where an exception holds up. It is the decision companion to Excel security problems developers overlook.

Which formats can carry code

The extension is a good first clue and a poor last word.

The xlsx specification (ECMA-376) has no place for a VBA project, so an application that saves a macro-enabled workbook as xlsx drops the macros. The macro-enabled formats store the code in a binary part, xl/vbaProject.bin in the zip formats, and declare a different content type in [Content_Types].xml. The file format description covers how the formats differ for readers.

What a macro can do that a pipeline never needs

A pipeline needs to read values, check them and load them. It does not need to run anything inside the workbook. A macro, by contrast, can open other files, make network requests, write to the registry, start processes and modify the workbook as it opens. Even a macro written in good faith, for instance one that refreshes a connection when the file opens, makes the file's behavior depend on the machine that opens it.

Libraries that read workbooks, such as openpyxl, pandas and DuckDB's Excel reader, do not execute macros. A pipeline built on them is not harmed by the code. The risk is to what happens next: the file is stored, emailed, shown in a preview or opened by a person, and the macro rides along. Accepting the file means taking responsibility for where it goes.

The built-in protections, and their gaps

Microsoft blocks macros in files that came from the internet. A file downloaded by a browser or received as an email attachment carries a "mark of the web" in the filesystem, and Office refuses to run its macros, showing a banner with no easy option to enable them. The company's guidance on this is in Macros from the internet are blocked by default.

The gaps are real:

• A file extracted from a zip archive may lose the mark, depending on the extraction tool.

• A file copied from a network share, a sync client or a USB drive may not have it.

• A file written by a script on a server has no mark, because nothing downloaded it.

• Files in "trusted locations" are exempt by design.

So a pipeline that receives files, unpacks them and drops them on a share can strip protection that the sender's browser applied. Relying on the desktop default makes your safety depend on every handoff in the chain. The policy should live where you control it, at the point of ingestion.

A policy that works

Write the policy in four lines, and enforce it in code.

1. Accept .xlsx and .csv. For inbound data, that is nearly always enough.

2. Reject .xlsm, .xlsb, .xls, .xltm and anything else, with a message that tells the sender how to resend (Save As, Excel Workbook).

3. Verify by content. Open the zip and refuse the file if it contains a vbaProject.bin part or declares a macro content type.

4. Keep an exceptions list with an owner and a review date.

The content check is short:

import zipfile

MACRO_PARTS = ("xl/vbaProject.bin", "xl/vbaProjectSignature.bin")
MACRO_TYPES = ("macroEnabled", "vbaProject")

def has_macros(path):
    with zipfile.ZipFile(path) as z:
        names = set(z.namelist())
        if any(p in names for p in MACRO_PARTS):
            return True
        ct = z.read("[Content_Types].xml").decode("utf-8", "replace")
        return any(t in ct for t in MACRO_TYPES)

A legacy .xls file is not a zip, so zipfile.ZipFile raises BadZipFile, which is the reason to reject it on format alone rather than inspect it. Combine this with the other upload checks, such as size and entry limits, in checking an Excel upload before reading it.

Convert instead of refusing, when you can

Sometimes the sender needs the data processed and cannot easily resend. A conversion step in a sandboxed environment can turn a legacy file into a clean xlsx, dropping the macros. LibreOffice running headless is the common tool, in a container with no network and a short timeout:

soffice --headless --convert-to xlsx --outdir out/ upload.xls

Treat the converter as a parser of hostile input: run it as an unprivileged user, cap its memory and time, and discard the original afterward. The converted file then passes through the same checks as any other upload. See batch converting xls files to xlsx for the mechanics.

Enforcing it on desktops

If your data team opens received files by hand, group policy settings control how Office behaves: block macros in files from the internet, disable all macros without notification as the default, restrict trusted locations to a short list and turn off dynamic data exchange. Your IT group owns those settings, and the data team's job is to say what it needs. A reasonable request is "no macros anywhere in the data team's Office installs except for a named exception". It is easier to ask for that before an incident than after one.

Replacing the macro

Most macros in data workflows do one of a few things: refresh a connection, copy data between sheets, format a report, export to CSV or run a loop over files. Each has a replacement that is easier to review and does not travel inside the document:

• Refreshing connections and reshaping data fits Power Query, which is visible and repeatable.

• Formatting and exporting a report fits a script that writes the workbook (automating weekly Excel reports with Python).

• Looping over files fits a script or a scheduled job that never opens Excel.

• Interactions inside the sheet, such as a button that clears an input area, fit Office Scripts for the web or a plain instruction (Office Scripts for Excel on the web).

A macro's author often left the company, and a script in version control has history and a reviewer. The migration costs a few days per macro, and it pays back the first time the old one breaks on a new Excel version.

Exceptions that hold up

Sometimes a macro stays. A finance model might have a solver routine that nobody wants to rewrite this quarter. To keep that honest, give each exception:

• A named owner, who answers when it breaks or misbehaves.

• A copy of the VBA source in version control, exported from the file, so changes are reviewed as text.

• A code signature from a certificate your organization controls, with the policy set to run only signed macros.

• A defined location: it opens from a trusted folder on one machine or a locked share, not from email.

• A review date, after which it is either renewed or retired.

Without those, the macro is unmaintained code with write access to files and a long life.

When allowing macros is justified

Allowing macros is justified when the workbook is a closed tool used by a small, trained group, produced and signed by that group, and never received from outside. It is justified when the alternative is a larger risk, such as a manual process that people would otherwise do by hand with copy and paste. And it is justified when the cost of replacement is high and the exposure is low. It is not justified for any file arriving from customers, partners, email or a public upload, and it is not justified for a file whose author cannot be identified. The default for a data pipeline is to refuse, convert or ignore, and to make the exception the thing that needs a reason.