Schedule an Excel Power Query Refresh for a Closed Workbook
Question details
Schedule Power Query connections to refresh without manually opening the Excel workbook.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants an unattended refresh on a schedule while the workbook is not already open.
- Observed behavior
- A Workbook_Open macro refreshes queries only after Excel opens the workbook; it cannot run while the file remains closed.
Confirm that the workbook refreshes successfully in Excel and that its data-source credentials, privacy settings, and permissions are configured. For the scheduled Windows method, check that Excel is installed and the scheduled user account can access the workbook and its data sources.
Schedule Windows to open and refresh the Excel workbook
Use Task Scheduler and an Excel automation script when you need a refresh without manually opening the file.
The workbook does not refresh while it remains closed: the scheduled process opens it in Excel, refreshes it, saves it, and closes it.
Excel desktop automation is not a guaranteed unattended service. Prompts, expired credentials, unsupported connections, or background refreshes can prevent a successful run, so test the complete process with the scheduled account.
Open Notepad and save the following as C:\Scripts\RefreshWorkbook.ps1, replacing the workbook path with your own: $excel = $null $book = $null try { $excel = New-Object -ComObject Excel.Application $excel.Visible = $false $book = $excel.Workbooks.Open('C:\Reports\Workbook.xlsx') foreach ($connection in $book.Connections) { try { $connection.OLEDBConnection.BackgroundQuery = $false } catch {} try { $connection.ODBCConnection.BackgroundQuery = $false } catch {} } $book.RefreshAll() $excel.CalculateUntilAsyncQueriesDone() $book.Save() } finally { if ($book -ne $null) { $book.Close($false) } if ($excel -ne $null) { $excel.Quit() } }
Sign in as the account that will run the task, close the workbook, and run the script from PowerShell. Open the saved workbook and verify that the query results and refresh times are current.
Open Task Scheduler, select Create Task, and choose the account used for the test. On the General tab, select Run only when user is logged on; on the Triggers tab, add the desired schedule.
On the Actions tab, select New, choose Start a program, and enter powershell.exe as the program. In Add arguments, enter -NoProfile -File "C:\Scripts\RefreshWorkbook.ps1", using the actual script path.
Right-click the task in Task Scheduler and select Run. Check Last Run Result, then open the workbook to verify that the expected data was refreshed and saved.

Refresh queries when the Excel workbook opens
Use a Workbook_Open macro if refreshing on opening is sufficient and a closed-workbook schedule is not required.
Work with Excel files in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook work from Microsoft Office. It does not fix Excel's scheduled Power Query refresh.

Frequently Asked Questions
Can Excel refresh a Power Query while the workbook stays closed?
A Workbook_Open macro cannot run while the file is closed. A scheduled desktop process must open the workbook in Excel, refresh it, save it, and close it.
Why did the scheduled task run without updating the data?
Check the workbook after the run for current results. A credentials prompt, inaccessible data source, privacy setting, or query still running in the background can prevent a complete refresh even if Task Scheduler reports that the process ran.
Does the Workbook_Open macro need a special file format?
Yes. Save the workbook through File > Save As as an Excel Macro-Enabled Workbook (*.xlsm), and allow macros when opening the file only if you trust it.
Can Office Scripts refresh every Power Query connection on a schedule?
No. Office Scripts and Excel for the web have limitations on refreshing external data connections. Check Microsoft’s current documentation and test your specific connection before relying on a scheduled Power Automate flow.
Will the scheduled Windows task work when I am signed out?
The example uses Task Scheduler's Run only when user is logged on option. Excel desktop automation can encounter prompts or session restrictions, so test the task under the account and session conditions in which it will run.




