Co‑authoring Excel Workbooks with VBA and Python Without Breaking Everything

The Scenario: A Monthly Finance Report Going Sideways

Imagine a small finance team:

• They maintain a Monthly Performance Report in Excel.
• The workbook has:
  • VBA for refreshing queries, formatting, and distributing PDFs.
  • Python scripts for heavier data prep (CSV dumps, API pulls, ML scoring).
• The file lives in SharePoint/OneDrive and is co‑authored by 4–5 people.

The pain points:

• Someone adds a new VBA reference on their machine → others get "Missing: XYZ".
• Python script writes new sheets or renames tables → VBA code breaks.
• Users see scary macro security warnings after Python regenerates the file.
• Two people edit code at once → changes are silently overwritten.

We’ll fix this by treating the workbook as a shared application with rules, not a random file everyone pokes.

Step 1: Separate Responsibilities Between VBA and Python

Most breakage comes from VBA and Python both thinking they "own" the workbook.

For the finance report, define a clear contract:

• Python owns:
  • Raw data ingestion (APIs, CSV, databases)
  • Heavy transformations (joins, aggregations, model scoring)
  • Writing data into staging sheets or tables
• VBA owns:
  • User triggers (buttons, menu items)
  • Refresh orchestration (calling Python if needed)
  • Formatting, charts, and final report layout
  • Distribution (export to PDF, email, etc.)

Make the contract explicit

Create a short README sheet in the workbook:

• "Python writes to sheets: stg_Sales, stg_Costs. Do not edit manually."
• "VBA reads from staging sheets and writes to Report and Dashboard."
• "Do not rename these sheets or tables without updating both VBA and Python."

This alone removes a lot of accidental damage.

Step 2: Lock Down Workbook Structure

Co‑authoring fails when structure drifts. You want predictable sheet names, table names, and named ranges.

Naming rules

Adopt conventions like:

• Staging sheets (Python): stg_* (e.g., stg_Sales)
• Report sheets (VBA): rpt_* (e.g., rpt_Monthly)
• Tables: tbl_* (e.g., tbl_SalesRaw)
• Named ranges: rng_*

Protect critical sheets

Use sheet protection (with a known shared password if needed):

Sub ProtectReportSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 4) = "rpt_" Then
            ws.Protect Password:="team123", UserInterfaceOnly:=True
        End If
    Next ws
End Sub

This stops casual renames and deletions that would break Python references.

Python: write into stable tables, not arbitrary ranges

Instead of Python dumping data into A1 with no structure, target a table:

import win32com.client as win32

excel = win32.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(r"C:\Reports\MonthlyReport.xlsm")
ws = wb.Worksheets("stg_Sales")
list_object = ws.ListObjects("tbl_SalesRaw")

data = [
    ["Region", "Month", "Revenue"],
    ["North", "2024-09", 120000],
    ["South", "2024-09", 95000],
]

# Resize table to match data
rows = len(data)
cols = len(data[0])
list_object.Resize(ws.Range(ws.Cells(1, 1), ws.Cells(rows, cols)))

# Write values
list_object.DataBodyRange.Value = data[1:]  # header excluded

wb.Close(SaveChanges=True)
excel.Quit()

VBA can now safely read tbl_SalesRaw without caring about last row/column.

Step 3: Avoid VBA Reference Nightmares

One of the biggest co‑authoring problems: references that work on one machine and fail on another.

Rules for VBA references

1. Prefer late binding for external libraries (e.g., Outlook, Scripting, ADO).
2. Avoid adding new COM references unless absolutely necessary.
3. Document required references on the README sheet.

Early binding (fragile) vs late binding (portable)

Fragile version:

' Requires Microsoft Scripting Runtime reference
Sub ListFilesEarlyBinding()
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    ' ...
End Sub

Portable version:

Sub ListFilesLateBinding()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' ...
End Sub

For the finance team, this means:

• The workbook opens cleanly on every machine.
• No one sees "Missing: Microsoft Scripting Runtime" after a library update.

Check references programmatically

Add a small diagnostic macro:

Sub CheckReferences()
    Dim ref As Reference
    For Each ref In ThisWorkbook.VBProject.References
        Debug.Print ref.Name, ref.Description, ref.BuiltIn, ref.IsBroken
    Next ref
End Sub

Run this when something breaks to identify problematic references.

Note: Accessing VBProject requires "Trust access to the VBA project object model" enabled in Macro Settings.

Step 4: Make Python Automation Workbook‑Friendly

Python can easily corrupt a macro workbook if it:

• Saves to a different file type.
• Strips VBA project or digital signature.
• Leaves the file open/locked.

Use the right file types

If the workbook contains macros:

• Use .xlsm or .xlsb.
• Do not save as .xlsx from Python unless you intentionally want to drop macros.

Example:

wb = excel.Workbooks.Open(r"C:\Reports\MonthlyReport.xlsm")
# ... update data ...
wb.Save()  # keep same format, keep macros/signature
wb.Close()

Avoid recreating the file when not necessary

Bad pattern:

# DON'T do this unless you know the impact
wb.SaveAs(r"C:\Reports\MonthlyReport_new.xlsx")

This can:

• Remove the VBA project entirely.
• Remove the digital signature and trigger security warnings.

Instead, update data in place and call Save().

If you must rebuild the file

Sometimes Python generates a fresh workbook (e.g. from a template). Then:

1. Keep a macro‑enabled template (.xlsm) with the VBA project and signed.
2. Python copies the template to a new file.
3. Python writes only data, never touches the VBA project.

import shutil

shutil.copy(
    r"C:\Templates\MonthlyReport_Template.xlsm",
    r"C:\Reports\MonthlyReport_2024_09.xlsm",
)

wb = excel.Workbooks.Open(r"C:\Reports\MonthlyReport_2024_09.xlsm")
# ... write data into staging sheets ...
wb.Save()
wb.Close()

The signature stays intact, and the security behavior is predictable.

Step 5: Tame Macro Security Warnings

Security prompts get worse when Python changes file locations or signatures.

Use a shared, trusted location

Agree on one path for the report, for example:

• A SharePoint library synced to C:\Users\...\SharePoint\FinanceReports

Then:

• Users add that folder to Trusted Locations in Excel.
• Python scripts read/write only within that folder.

This reduces prompts like:

• "Macros have been disabled"
• "File came from an Internet location" (if you avoid email attachments and Teams downloads for the master file).

Sign the macro project once, then don’t touch it

If your environment allows code signing:

1. Sign the VBA project in the template workbook.
2. Distribute only signed versions.
3. Make it a rule: no editing VBA in production files. Dev happens in a dev copy, then the signed version is updated.

Python must not:

• Save over the signed file with a different format.
• Remove or re‑create the workbook in a way that drops the signature.

Step 6: Version Control for Code, Not Just Files

Co‑authoring via SharePoint/OneDrive is okay for data and formulas, but it’s terrible for code.

For the finance team:

• The workbook lives in SharePoint.
• VBA code and Python scripts live in a Git repo.

Practical setup

1. Export VBA modules to .bas/.cls files.
2. Store those alongside .py scripts.
3. Use Git (or similar) for branching, reviews, and history.

Example folder structure:

FinanceReport/
  python/
    refresh_data.py
    scoring.py
  vba/
    modRefresh.bas
    modFormatting.bas
  docs/
    workflow.md

Simple VBA export/import workflow

Use a small helper macro during development (not in production):

Sub ExportModules()
    Dim vbComp As VBIDE.VBComponent
    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        If vbComp.Type = vbext_ct_StdModule Then
            vbComp.Export "C:\Repo\FinanceReport\vba\" & vbComp.Name & ".bas"
        End If
    Next vbComp
End Sub

You then commit the .bas files to Git. When another developer pulls changes, they import modules back into the workbook.

This avoids silent overwrites when two people edit VBA in the same file.

Step 7: Define a Safe Refresh Workflow for the Team

Now tie it all together for the monthly report.

Target workflow

1. Analyst clicks "Full Refresh" button in Excel.
2. VBA macro:
   • Calls Python (shell command or RunPython if using an add‑in).
   • Waits for Python to finish writing to staging sheets.
   • Runs transformations and formatting into report sheets.
   • Saves and (optionally) exports PDF.

VBA side:

Sub FullRefresh()
    Application.ScreenUpdating = False

    ' 1. Run Python
    Call RunPythonRefresh

    ' 2. Refresh report from staging tables
    Call BuildReportFromStaging

    ' 3. Save
    ThisWorkbook.Save

    Application.ScreenUpdating = True
End Sub

Sub RunPythonRefresh()
    Dim cmd As String
    cmd = "python ""C:\Repo\FinanceReport\python\refresh_data.py"""
    Shell cmd, vbNormalFocus
End Sub

Python side (simplified):

import win32com.client as win32
from time import sleep

excel = win32.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(r"C:\Reports\MonthlyReport.xlsm")

# write to staging tables only
update_sales_staging(wb)
update_costs_staging(wb)

wb.Save()
wb.Close()
excel.Quit()

Team rules to avoid conflicts

• Only one person runs Full Refresh on the master file at a time.
• Others work on copies for analysis; only the owner updates the master.
• Code changes go through Git; the master file only gets updated VBA after review.

One Concrete Takeaway: Write a Contract Sheet

If you do nothing else, add a "Technical Contract" sheet to your shared workbook and fill it with:

• Which sheets/tables Python owns
• Which sheets/tables VBA owns
• Naming conventions
• Required references and locations
• The agreed refresh workflow

Then share that sheet in your team chat and stick to it. Co‑authoring Excel workbooks with both VBA and Python stops being chaos when everyone can see, in one place, what they’re allowed to touch and what they must not break.