Expert guide
Excel Automation for Ops Teams: VBA vs Power Automate vs Apps Script
Pick the tool that matches the bottleneck - then know when DIY is costing more than a fixed-scope build
Operations teams automate Excel for one reason: the same file is rebuilt every week. The wrong question is 'which tool is modern.' The right question is where the data lives, who must run the job, and what happens when it fails at 7 a.m. before the warehouse opens. This guide is for ops managers and controllers who already live in workbooks - not for developers collecting certifications.
Excel VBA, Microsoft Power Automate, and Google Apps Script all can move data. They fail in different ways. VBA dies when a colleague opens the file on Mac without macros. Power Automate dies when a connector quota or a renamed SharePoint column silently skips rows. Apps Script dies when the workbook is still an .xlsx on a file share that Google never sees. Choose the runtime that matches the file's real home.
Start with the bottleneck, not the logo
Write the job in one sentence: 'Every Monday we combine 12 branch CSVs into one pack and email exceptions to regional managers.' Then list constraints: Windows-only floor PCs, Microsoft 365 already paid, or a Google Workspace shop. Tool choice is a constraint problem. If you start from 'we should use Power Automate because it is cloud,' you will rebuild a process that still needs a desktop Excel model the floor will not abandon.
Data gravity
If the system of record is a desktop Access database or a network workbook, cloud flows add a copy. Copies drift.
Operator skill
If the person who runs the job cannot debug a flow, they need a button in the file they already open.
Failure visibility
Ops needs a failed-job that is obvious: a log sheet, an email, a red cell. Silent success checkmarks are how you ship the wrong inventory number.
When Excel VBA is still the right automation
VBA is the right default when the workbook is the application: a close file, a quoting model, a commission engine, a shop-floor tracker that must run with Excel open. It can loop files on a drive, talk to Access via DAO/ADO, call APIs, and write formatted packs. It does not require a Power Automate premium connector for a folder of CSVs on a local path.
VBA wins when
The file must format, validate, and produce a print-ready pack in one click.
You integrate with desktop Access, Outlook on the PC, or a local SQL DSN.
The team will not move the source files off a Windows share this quarter.
You need row-level logic that is painful in Power Query alone (nested allocations, draws, split commissions).
VBA fails when
Mac-only users must run the same macros (VBA for Excel on Mac is a subset; many shops still assume Windows).
IT blocks macros and will not sign them.
The job must run at 2 a.m. with nobody logged into a PC - unless you add a scheduled Windows task on a dedicated box.
Five people must edit the same workbook at once. That is an Access or SQL problem, not a macro problem.
For delivery, see Excel data integration and automation and MS Excel consultant. If the workbook is really a multi-user database, read when to replace a bloated Excel file with a database.
When Power Automate is the right automation
Power Automate is the right default when events already live in Microsoft 365: a file lands in SharePoint, a Form is submitted, an email arrives in a shared mailbox, a row is added in a List. Cloud flows are good at connectors and bad at replacing a 40-sheet financial model. Desktop flows (RPA) can click through a UI you do not control, but they are brittle and often more expensive than a VBA or API job that talks to a documented interface.
Power Automate wins when
SharePoint or OneDrive is already the file home and IT will allow the connections.
You need approvals, Teams notifications, or a Forms-to-Excel capture without a desktop macro.
The transformation is light: move, rename, notify, append to a table.
Power Automate fails when
The real work is Excel calculation logic that already exists in the workbook. Recreating it in flow expressions is a rewrite, not an automation.
Premium connectors or attended RPA licenses were not in the budget.
Column names in SharePoint change and nobody owns the flow. Ops will not open Power Automate to debug.
You need pixel-perfect Excel output. Flows can write tables; they are weak at the last-mile formatting controllers expect.
A durable pattern we use: Power Query or VBA produces the pack; Power Automate only moves the finished file and notifies. Do not put the entire close in a flow.
When Google Apps Script is the right automation
Apps Script is the right default when the system of record is Google Sheets and Workspace: Drive folders, Gmail, Google Forms, Calendar. It is not a way to 'modernize' a 200-tab .xlsx that accounting will keep on a Windows share. If the file is not in Drive, Apps Script is the wrong runtime.
Apps Script wins when
The team already collaborates in Sheets with multiple editors.
You need Gmail-to-sheet capture, Drive folder processing, or Forms-to-register automation.
IT will not allow Excel macros but will allow Workspace.
Apps Script fails when
The workbook must stay Excel-compatible for a customer or a bank that only accepts .xlsx.
You need deep Access or SQL Server integration. Apps Script can call APIs; it is not DAO.
Execution time quotas bite on large loops. Heavy jobs need batching or a proper database.
Gmail-to-sheet style capture is a real ops pattern; see Gmail to Excel automation when the destination is Excel rather than Sheets.
Power Query sits in the middle (and is often enough)
Before you write VBA or a flow, ask whether Power Query can land the data. Combining folders of CSVs, unpivoting, and loading a table is a refresh, not a program. Ops teams over-automate when a Get Data from Folder would remove 80% of the work. Query is not enough when you need to write back to many files, drive a UI, or enforce data-entry rules on a form.
A practical sequence: (1) Power Query to assemble, (2) worksheet logic to calculate, (3) VBA or a flow only for the last mile you cannot refresh. Skip straight to RPA and you will pay for clicks that a query would have replaced.
Decision table for ops leads
Local Excel application, Windows users, click-to-run
VBA (optionally scheduled on a dedicated PC). Keep Power Query for extracts.
Files already in SharePoint; light move/notify
Power Automate cloud flow. Keep Excel for the model.
Google Workspace native
Apps Script plus Sheets. Do not dual-run Excel macros on the same process.
Multi-user concurrent edits, relational data
Access or SQL - not a bigger spreadsheet. See our Access services and the Access-to-SQL guide.
Unstructured documents (PDFs, emails) into tables
API extraction with validation - see AI-powered workflow automation - then land in Excel/Access.
Cost of DIY vs a fixed-scope project
DIY is correct for a one-person weekly cleanup. It is expensive when three people maintain competing macros, nobody documents the refresh, and month-end slips a day. Price the leak: people × hours × fully loaded wage. If that annual number is a multiple of a typical project (ours usually start at $1,200 after review), stop treating automation as a side quest.
Don't want to DIY? Book a 15-minute automation audit for a fixed-scope Excel or Access project. Quantify the leak first with the free Workflow Cost Audit.
Failure modes we see on live ops files
Macro-enabled files emailed as attachments, so five versions exist by Thursday.
A Power Automate flow 'succeeds' after writing zero rows because a filter is wrong.
Apps Script hitting six-minute execution limits on a sheet that should have been a database years ago.
VBA that assumes a drive letter the new laptop does not have.
No log sheet: when it fails, the first signal is a customer complaint.
Hardening is not optional: versioned files in one library, a run log, and an owner. If nobody can say who is allowed to change the rate table, you do not have automation. You have a risk.
Where AI fits (and where it does not)
Language models help when the input is messy text or PDFs. They do not replace a trial-balance tie-out. If you need extraction or routing into Excel/Access, that is a scoped AI-powered workflow automation project on top of the runtime you already chose - not a ChatGPT window beside the close file.
A 30-day path if you are drowning this month
Week 1: Write the one-sentence job and the annual labor cost (use the cost audit).
Week 2: Put source files in one place (SharePoint, Drive, or a single network folder). Stop emailing the master.
Week 3: Power Query or a documented import. Kill copy-paste between workbooks.
Week 4: Add only the runtime you need (VBA button, one flow, or one script). Log the run.
If week 3 still requires three people in the same file, stop. That is a database. Automating a collision is not ops excellence.
Who this guide is for
Ops, finance, and office managers who own a weekly pack. It is not a tutorial for 'what is Excel.' If you need someone to implement the path as a written, fixed-scope build, that is consulting - not a blog DIY. If you need an industry-shaped system (healthcare billing, warehouse SKUs, CPA packs, job cost, law-firm billing), start on the matching solutions hub rather than a generic macro.