Automating the Workbook That Runs the Business

They are load-bearing more often than admitted

Plenty of profitable businesses run a core process in a shared workbook. Automating around it — reading from it, writing to it, validating it — is frequently more useful than replacing it.

That approach requires nobody to change how they work, which is why it is adopted where a replacement would not be.

What makes them awkward

1. Merged cells that break row alignment
2. Several tables on one sheet with blank rows between
3. Headers repeated partway down
4. Numbers stored as text, or with stray characters
5. Dates in several formats within one column

Code that assumes a clean rectangular table with one header row will work on the sample file and fail on the real one. Every time.

Validate as you read

• Check the expected columns exist, by name rather than position
• Check types — a number column containing text is a signal
• Check ranges — dates and amounts within plausible bounds
• Check the row count against expectation
• Report anything unexpected rather than processing it

Writing back

Writing to a workbook people also edit is where problems arise: someone has it open, formulas get overwritten, formatting is lost.

Prefer writing to a separate output file, or to a dedicated sheet nobody edits by hand. Overwriting a workbook someone is working in is a good way to lose a morning's work.

When to move on from spreadsheets

Two or more of those and a proper system is worth pricing. Fewer, and automating around the spreadsheet is the cheaper answer.

Frequently asked questions