Running real Excel VBA macros in the cloud (without rewriting them)

A lot of small businesses run on one Excel macro somebody wrote years ago. In a bookkeeping team I worked with, that macro turns a raw bank export into the clean statement file the month-end process depends on. It works. Nobody wants to rewrite it. But somebody still has to sit at a PC every month, open each bank file, delete the extra columns, run the macro, and save the result in the right folder.

I wanted the macro to run on its own, right after each bank export, without changing a single line of it. That ruled out the usual answers: rewriting it in Python, using LibreOffice, or using an Excel library that reads and writes files without running VBA. If the output isn't identical to what the team gets by hand, nobody will trust it.

So real Excel had to run somewhere. This post is how that works and which parts took longest to get right.

The shape of it

There are three pieces:

1. The web app (a Flask app on a small Linux server) creates a "macro job": the bank file, the team's .bas module, and any workbooks the macro opens.
2. A Windows VM with licensed Excel runs a small PowerShell agent. It asks the server for the next job, runs it in Excel, and uploads the result.
3. The server starts and stops the VM so it only runs while there's work.

The VM never gets bank or cloud-storage credentials. It only sees the files of the job it's holding, and it talks to the server with a single bearer token.

Excel needs a desktop

The first thing you learn: Excel automation (COM) doesn't really work from a service or an SSH session. It needs an interactive desktop session, the same as a person sitting at the keyboard.

So the VM signs in a standard (non-admin) Windows user at boot, using Sysinternals Autologon (which stores the password as an LSA secret, not in plain text). A scheduled task starts the agent when that user signs in. Excel runs as that user, never as the built-in Administrator. That also turned out to matter for licensing: the built-in Administrator account couldn't use the work-account sign-in at all, so Office only offered personal accounts there.

One more setting: the macro is imported into the workbook at run time, so Excel needs "Trust access to the VBA project object model" turned on for that user.

Running the macro unchanged

For each job the agent:

1. Opens the input workbook.
2. Imports the .bas module.
3. Runs the entry macro by name.
4. Turns DisplayAlerts off again, because the macro turns it back on.
5. Removes the module, saves in the right format (.xlsb, .xlsx or .xlsm) and uploads it.

Before any of that, the server does the boring "prepare" step a person used to do by hand. Bank exports arrive with different column names ("Transaction Date", "Posting Date", "Description", "Payee"…), so it matches the headers and builds the exact sheet layout the macro expects. Debit/Credit files get the team's merge macro first, single-amount files don't. If a file has columns it doesn't recognise, the job is refused with the list of headers it found, instead of producing a wrong file quietly.

The server also reads the .bas file and follows which subs call which, to find any hard-coded paths the macro opens (for example a client list workbook). If one of those files hasn't been uploaded, the job is refused before Excel ever starts.

The result: on a real month of data, the cloud run produced a sheet identical to the one the team made by hand: same 135 rows, same net total.

The pop-up problem

This took longer than everything else combined.

Macros written for people talk to people: MsgBox "Folder created successfully!". When no one is there to click OK, Excel just waits. Forever.

The agent runs a small helper that watches for dialog boxes owned by Excel and answers them. Three lessons:

• Never read window text with a plain SendMessage. If any window on the desktop is hung, SendMessage hangs with it, and so does your watcher. Use SendMessageTimeout with SMTO_ABORTIFHUNG and a short timeout.
• Pick the button by its text. My first version "clicked" the dialog's default button by posting a command with its ID. It didn't close a VBA message box. Now it finds the button by label (OK, Yes, Continue, Close…), sends the click, and retries every few seconds.
• On a VBA run-time error, press End, never Debug. Debug opens the VBA editor and the job is stuck for good. End stops the macro, and the job fails with a clear message.

There's also a hard timeout: if a job runs longer than 10 minutes, Excel is killed and the job is marked failed.

Running only when there's work

Bank exports don't arrive all day, so there's no reason for a Windows machine to sit idle waiting for them. So:

• The VM is an Azure Spot instance (2 vCPU, 16 GB). The catch with Spot is that Azure can take the machine back at any time ("eviction"), so the system has to cope with that.
• The server starts it when there are jobs waiting and the agent hasn't checked in, and deallocates it after 5 idle minutes. The server uses a service principal with a custom role that can only read, start and deallocate VMs in one resource group. It can't create or delete anything.
• Evictions are handled like crashes. If a running job stops sending heartbeats for 5 minutes, it goes back in the queue, up to 3 attempts, then fails with a message.
• A start takes about 2¼ minutes, plus about a minute for sign-in and the agent. A typical job then takes about 70 seconds, and around 30 seconds per file once Excel is warm.

Bank exports now queue their statement files while the other accounts are still downloading, so the VM is already booting by the time they're needed.

Things I'd tell anyone trying this

• Licensing is the first question, not the last. Running Office on a shared or cloud machine needs Shared Computer Activation, and only some Microsoft 365 business plans allow it. Check the terms for your plan, and talk to a Microsoft partner before running files for many different clients on one account.
• Keep the licence warm. Shared activation tokens expire after about 30 days, so the VM starts once a week and opens Excel briefly, even when there's no work.
• Stop, don't delete. I once deleted the VM from the portal by mistake. The disk survived, so recreating it from the same disk took one command, but jobs waited silently until I did. Now any error from the start/stop loop sends an email.
• Test macro changes before they go live. Every new version of the .bas file is run on a past job's input next to the current version, and the two outputs are compared row by row. Only then do you switch.

Was it worth it?

For a process that repeats every month, across many bank accounts, yes. The team keeps their macro exactly as it is, and the files appear in their Dropbox folder on their own. When something does go wrong, it fails loudly with a reason instead of producing a file that looks right but isn't.

If you have a macro like this, or another manual process that eats your week, tell us about it.