Scheduled Xero Report Refresh in Excel

Every scheduled-refresh pattern works the same way: an automation opens the file, Excel recalculates the formulas (pulling fresh Xero data through the XO Report add-in), and the automation saves the file. For this to work the workbook must recalculate on file open.

Excel desktop has this on by default — File → Options → Formulas → Workbook Calculation set to Automatic. Excel for Web defaults to automatic too. If a previous user set it to Manual, change it back; otherwise the workbook opens with stale numbers and the automation has accomplished nothing.

Sanity check: open the file in Excel, change a date in the Setup tab, and confirm the dependent XO.BALANCE formulas re-pull from Xero. If they do, the same recalculation trigger fires on file open and the schedule patterns below will keep the workbook current.

The simplest pattern for a single-PC setup. Windows Task Scheduler ships with every Windows install; the cost is zero. Open Task Scheduler, create a new task, set the trigger to your desired schedule (daily at 06:00 is typical for morning-pack delivery), set the action to Start a program, browse to excel.exe (commonly C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE), and pass the workbook path as the argument.

Excel opens, recalculates, the XO Report add-in fetches fresh Xero data, and the workbook is current. To save the file in place, either rely on Excel's AutoSave (when the file is on OneDrive / SharePoint) or run a small companion PowerShell that calls $wb.Save() and $xl.Quit()via the Excel COM object. Trade-offs: the PC must be on and signed in; the user account running the task needs the same Xero OAuth tokens (so run as the user who installed XO Report). Best for a single analyst's working file.

The cloud-native pattern. Save the workbook to OneDrive for Business or SharePoint, then write a small Office Script (TypeScript-flavoured Excel automation that runs in Excel for Web). The script wakes Excel up, recalculates, and saves:

function main(workbook: ExcelScript.Workbook) {
  workbook.getApplication().calculate(ExcelScript.CalculationType.full);
  // The file is saved automatically by AutoSave when stored on OneDrive/SharePoint.
}

Then create a Power Automate Scheduled cloud flow with two actions: (1)Run scriptagainst your workbook on OneDrive / SharePoint; (2) a follow-up notification action if you want a Teams message on success. The flow runs in Microsoft 365 cloud — no local PC needed. Cost: an Office 365 Business Standard licence covers Power Automate's standard connectors, which is what this flow uses.

Trade-off: XO Report custom functions need to be authorised for the Excel for Web session running the script. The first run typically requires the user account owning the OneDrive file to have approved the add-in's Excel-for-Web permission once. After that the scheduled flow runs unattended.

If your team already runs a workflow tool, you can probably reuse it. Power Automate Desktop excels at the Pattern A trick of opening Excel on a PC, but on a schedule managed centrally rather than per-PC. Make and n8n can hit the Microsoft Graph API to trigger the same Office Scripts run that Pattern B uses, with more elaborate downstream actions — fire a Slack message, write a CSV snapshot to S3, push a row to a Postgres table.

The lowest-friction implementation in this family is usually: an n8n cron node calls Microsoft Graph "run script" on the OneDrive workbook, the script recalculates and saves, then the workflow can optionally read the workbook range and forward the numbers wherever you need them.

Trade-off: any pattern that involves the Microsoft Graph API needs an admin consent for the app registration, which means tenant admins have to approve it once. Pattern A avoids that ceremony entirely; Patterns B and C trade it for the ability to run unattended in the cloud.

None of these patterns are inherently better than the others — they suit different setups. A 60-second decision tree:

• File lives on a single PC, only one analyst opens it. Pattern A (Task Scheduler). Cheapest, no admin involvement, but breaks when the PC is off.
• File lives on OneDrive / SharePoint, multi-user finance team. Pattern B (Office Scripts + Power Automate). Survives any one user being on holiday because the schedule runs server-side.
• Team already runs Make / n8n / Zapier for other automation. Pattern C. Reuse the orchestration you have; add downstream actions (Slack, database, S3) without inventing new infrastructure.

Whichever pattern you pick, monitor it. The most common failure mode is a Xero OAuth token expiring or the Excel add-in needing re-authorisation. Add a tiny check at the end of the script — fetch a single cell and assert it is a number, not the #N/A that XO Report returns for an unauthorised call — and alert when it fails.