I Tried Automating the Same Excel Report with VBA, Python, Zapier, and an AI Agent — Here's What Happened

I spend a lot of time talking about Excel automation. So I decided to stop talking and start testing. One task, four tools, timed from start to finish. No cherry-picking results.

Here's what actually happened.

The Task

Every month, our operations team receives five regional sales files — North, South, East, West, and Central. Each is an Excel workbook with a "Sales" sheet containing columns for date, product, units sold, and revenue. The task:

1. Consolidate all five files into a single summary workbook
2. Add a totals row at the bottom
3. Calculate region-by-region breakdown (total revenue per region)
4. Export the summary as PDF
5. Email it to the management team

This is not a hypothetical. Thousands of teams do exactly this every month. It takes about 45 minutes by hand — longer if a file is late or formatted wrong.

Let's see how each tool handles it.

Attempt 1: VBA

Time to working result: 3 hours 20 minutes

I opened the Visual Basic Editor and got to work. Here's the core of what I wrote:

Sub ConsolidateRegionalSales()
    Dim folderPath As String
    Dim fileName As String
    Dim wbSource As Workbook
    Dim wsSummary As Worksheet
    Dim nextRow As Long

    folderPath = "C:\Reports\Regional\"
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    nextRow = 2

    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set wbSource = Workbooks.Open(folderPath & fileName)

        Dim lastRow As Long
        lastRow = wbSource.Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Row

        wbSource.Sheets("Sales").Range("A2:D" & lastRow).Copy _
            Destination:=wsSummary.Range("A" & nextRow)

        ' Add region name in column E
        wsSummary.Range("E" & nextRow & ":E" & nextRow + lastRow - 2).Value = _
            Replace(Replace(fileName, ".xlsx", ""), "_Sales", "")

        nextRow = nextRow + lastRow - 1
        wbSource.Close SaveChanges:=False
        fileName = Dir
    Loop

    ' Add totals row
    wsSummary.Range("C" & nextRow).Formula = "=SUM(C2:C" & nextRow - 1 & ")"
    wsSummary.Range("D" & nextRow).Formula = "=SUM(D2:D" & nextRow - 1 & ")"
    wsSummary.Range("A" & nextRow).Value = "TOTAL"
End Sub

That's the consolidation part. I still needed separate code for the region breakdown, the PDF export, and the email via Outlook. The full macro ended up at around 120 lines.

Pain points

• Debugging was slow. VBA's error messages are cryptic. A mismatched sheet name killed 40 minutes.
• File paths are hardcoded. Move the files to a different folder and the macro breaks.
• Email required Outlook automation. That's another layer of COM references and security prompts.
• PDF export worked — but formatting was inconsistent across different Excel versions.
• No one else can maintain this. When I leave, this macro becomes a mystery.

Verdict

VBA gets the job done if you have the patience to write and debug it. But it's brittle, machine-specific, and assumes the person maintaining it knows VBA. For a one-person operation, maybe fine. For a team, risky.

Attempt 2: Python (openpyxl + smtplib)

Time to working result: 2 hours 10 minutes

Python felt more natural. I used openpyxl for Excel manipulation and smtplib for email.

import openpyxl
from pathlib import Path
from openpyxl.utils.dataframe import dataframe_to_rows
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders

folder = Path("./regional_reports/")
summary_wb = openpyxl.Workbook()
summary_ws = summary_wb.active
summary_ws.title = "Summary"
summary_ws.append(["Date", "Product", "Units", "Revenue", "Region"])

for file in folder.glob("*.xlsx"):
    region = file.stem.replace("_Sales", "")
    wb = openpyxl.load_workbook(file)
    ws = wb["Sales"]
    for row in ws.iter_rows(min_row=2, values_only=True):
        summary_ws.append(list(row) + [region])

# Add totals
last_row = summary_ws.max_row
summary_ws.append([
    "TOTAL", "",
    f"=SUM(C2:C{last_row})",
    f"=SUM(D2:D{last_row})",
    ""
])

summary_wb.save("consolidated_report.xlsx")

Then a separate script for PDF conversion (which requires either win32com on Windows or LibreOffice headless on other platforms) and another block for emailing.

Pain points

• PDF conversion is a mess. There's no clean, cross-platform way to convert .xlsx to PDF in Python. I ended up shelling out to LibreOffice — which means installing LibreOffice on every machine that runs this.
• Email configuration is fiddly. SMTP credentials, app passwords, TLS settings — it works, but it took 30 minutes to get right.
• Dependencies. openpyxl, smtplib (built-in, at least), LibreOffice. Each is a potential failure point.
• No scheduling built in. You need cron (Linux/Mac) or Task Scheduler (Windows) to run this monthly.

Verdict

Python is more flexible than VBA and the code is more readable. But the toolchain is heavier. You need Python installed, libraries managed, and a way to schedule execution. Great if you have a developer on the team. Overkill if you don't.

Attempt 3: Zapier

Time to working result: 1 hour 45 minutes

Zapier promises no-code automation. I set up a Zap with these steps:

1. Trigger: Schedule — 3rd of every month at 9 AM
2. Action 1: Google Drive — find files in "Regional Reports" folder
3. Action 2: Formatter — parse Excel data
4. Action 3: Google Sheets — write consolidated data to a new sheet
5. Action 4: Gmail — send the sheet as attachment

Pain points

• Zapier can't read .xlsx files natively. I had to convert them to Google Sheets first — which meant either uploading manually or adding a conversion step. This ate 30 minutes.
• No real Excel processing. Zapier moves data between apps. It doesn't calculate totals, create formulas, or format spreadsheets. I needed a Google Sheets intermediary to do the actual consolidation.
• The "loop through 5 files" logic was awkward. Zapier's looping features exist but aren't intuitive. I ended up with a multi-path Zap that felt over-engineered for such a simple task.
• PDF export required yet another step. I used a third-party Zapier integration to convert the Google Sheet to PDF. That's another dependency and another potential failure point.
• Cost adds up. This Zap uses 6-8 tasks per run. At Zapier's pricing, that's fine for one report — but scale to 10 reports and you're on a $50+/month plan.

Verdict

Zapier is excellent for connecting apps — "when X happens in App A, do Y in App B." But consolidating and processing Excel data is not what it was built for. I spent more time working around its limitations than actually solving the problem.

Attempt 4: Reflexion (AI Agent)

Time to working result: 12 minutes

I connected my OneDrive (where the regional files live) and typed this:

"On the 3rd of each month, pull the five regional sales files from the 'Regional Reports' folder in OneDrive. Consolidate all the Sales sheets into one summary workbook with a Region column. Add a totals row. Create a region-by-region revenue breakdown. Export as PDF and email it to management@company.com with the subject 'Monthly Sales Summary — [Month Year]'."

Reflexion asked two clarifying questions:

1. "Should the region breakdown be a separate sheet or a section at the bottom of the summary?"
2. "Should I include unit totals in the breakdown or just revenue?"

I answered: separate sheet, revenue only.

It ran a test with last month's files. The output had the summary, the breakdown, the totals — all formatted cleanly. The PDF looked exactly like what I'd produce manually.

I approved, and the workflow was scheduled.

Pain points

• Honestly, not many. The biggest adjustment was trusting the output without having written the logic myself. The human-in-the-loop review step helped — I could see exactly what would be sent before it went out.
• I wanted to tweak the PDF formatting. I told Reflexion "make the header row dark blue with white text" and it adjusted. But I did have to ask — it didn't guess my aesthetic preferences.

Verdict

Twelve minutes from start to scheduled, recurring automation. No code, no dependencies, no maintenance to worry about. The output matched what I'd produce by hand.

The Results

	VBA	Python	Zapier	Reflexion
Setup time	3h 20m	2h 10m	1h 45m	12 min
Code/config required	~120 lines VBA	~80 lines Python + LibreOffice	6-step Zap + Google Sheets workaround	Natural language prompt
Technical skill needed	VBA proficiency	Python + CLI + cron	Low (but Zapier logic model)	None
PDF export	Built-in but inconsistent	Requires LibreOffice	Third-party integration	Built-in
Email delivery	Outlook COM automation	SMTP configuration	Native Gmail integration	Built-in
Scheduling	Manual or Task Scheduler	Cron / Task Scheduler	Built-in	Built-in
Handles format changes	Breaks	Breaks	Breaks	Adapts
Maintenance burden	High	Medium	Medium	Low
Monthly cost	Free (your time)	Free (your time)	$20-50/month	Per-task pricing

What Surprised Me

VBA was the most painful. I expected it to be tedious, but the debugging cycle — write, run, error, guess, fix, run again — was genuinely exhausting. Modern developers forget how bad the VBA IDE is.

Python was the most satisfying to write — but only because I already know Python. If I didn't, this would have been the longest attempt by far. And the PDF conversion problem has no clean solution.

Zapier was the most frustrating. Not because it's bad — it's great at what it does. But what it does is connect apps, not process data. Trying to use it for Excel consolidation felt like using a screwdriver as a hammer.

Reflexion was the fastest by a wide margin — and the output quality matched or exceeded the others. The clarifying questions it asked were exactly the questions a competent assistant would ask.

Who Should Use What

Use VBA if you're already comfortable with it, the workbook is self-contained, and you're the only one who needs to run or maintain it.

Use Python if you have developer skills on the team, you need maximum flexibility, and you're comfortable managing dependencies and scheduling.

Use Zapier if your automation is primarily about connecting apps — sending notifications, syncing data between platforms, triggering actions. Just don't try to make it do heavy data processing.

Use an AI agent like Reflexion if you want the fastest path from "I need this report automated" to "it's automated." No code, no maintenance, no learning curve. Especially suited for teams without a developer and for workflows that change over time. For a deeper look at how Reflexion handles Excel automation, see How Reflexion Automates Your Monthly Excel Reports.

The Bigger Picture

The real difference isn't speed — it's what happens next month. VBA, Python, and Zapier all require me to maintain the automation. If a file name changes, if a column is added, if someone renames a sheet — I'm debugging.

With an AI agent, I describe the intent: "consolidate regional sales data." The agent figures out the mechanics. When those mechanics change, it adapts. That's not a minor convenience — it's a fundamentally different maintenance model.

If you're still doing this kind of work by hand, any of these tools will save you time. But if you want to set it up once and move on, the choice is clear. For proven patterns you can apply regardless of which tool you choose, see Excel Reporting: 7 Automation Patterns. And if you're in distribution specifically, see 5 Excel Reports Every Wholesale Distributor Should Automate First.

---

Want to see how your specific report would work? Send us a sample file and we'll build a free automation map for your workflow. Or try Reflexion for free and automate your first report in minutes.

Related Reading

• Automate Excel Reports Without Writing VBA — Why AI agents are replacing macros
• AI Agents vs Power Query vs Power Automate — Detailed comparison of the Microsoft automation stack
• Excel Reporting: 7 Automation Patterns — Proven patterns for any automation approach
• How Reflexion Automates Your Monthly Excel Reports — Step-by-step tutorial