Killing Document Drudgery: Auto-Fill Word Templates with a Macro

The Problem: 40 Invoices a Week, All Identical

An accountant at a small logistics firm generated 40-50 invoices every week. Each one used the same Word template: company name, address, invoice number, date, line items, totals. Different data, same structure. The process: open template, fill in fields, save as PDF, rename file, attach to email. About 4-5 minutes each. That's over 3 hours a week on pure mechanical data entry.

The data lived in a simple Excel spreadsheet - one row per client, with all invoice fields as columns. Everything was already structured. It just needed to be moved from Excel to Word automatically.

The Approach

The macro workflow:

1. Excel is open, cursor on the first data row
2. Macro copies each cell in sequence (Ctrl+C, Tab to next, repeat)
3. Macro opens the Word template (Ctrl+O, navigate to file)
4. For each field: click placeholder text, then Ctrl+V to paste value
5. Save as PDF via File - Export
6. Return to Excel, move to next row
7. Repeat in a loop for all rows

Data Source Setup

We simplified the data source step by keeping a plain Notepad window open alongside Word, with one value per line. The macro reads from Notepad by clicking line 1, selecting all (Ctrl+A), copying - rather than navigating Excel columns. This is more reliable because Notepad has no formatting, no accidental formula triggers, no cell selection surprises.

# data.txt format (one invoice per block, blank line separator)
Acme Corp
123 Business Street
INV-2026-0041
February 26, 2026
Software License x1
1200.00

The accountant prepares this file from Excel using a simple copy-paste at the start of each batch. Takes 2 minutes instead of 3 hours.

Recording the Field-Filling Sequence

Word templates use placeholder text like [CLIENT_NAME]. The macro uses Ctrl+H (Find and Replace) rather than clicking each placeholder manually - this is much more reliable across document sizes.

action_type,button,x,y,delay_ms,comment
key,ctrl+h,,,200,Open Find and Replace
click,left,640,380,150,Click Find field
type,[CLIENT_NAME],,,50,Type placeholder
key,tab,,,80,Move to Replace field
key,ctrl+v,,,100,Paste actual value
key,alt+a,,,500,Replace All
key,escape,,,100,Close dialog

This sequence repeats for each field. The loop runs it for every client row in the data file.

Saving as PDF

After filling all fields, the macro triggers File, Export, Create PDF. In Word, this is: Alt+F, E, P, Enter. The file gets saved to a designated folder with today's date in the filename.

Results

Variations and Extensions

• Reports: Monthly status reports with fixed sections but variable numbers - same pattern, different template.
• Contracts: Standard contract templates with client details - works identically.
• Multi-page forms: If the document has multiple pages, use Ctrl+F to navigate to each field rather than scrolling.
• Email attachment: Extend the macro to open your email client and attach the generated PDF automatically.