Power Query Problems

Schedule an Excel Power Query Refresh for a Closed Workbook

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Schedule Power Query connections to refresh without manually opening the Excel workbook.

Schedule an Excel Power Query Refresh for a Closed 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a refresh script

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() } }

2
Test the script

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.

3
Create a scheduled task

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.

4
Add the PowerShell action

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.

5
Run and verify the task

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.

Schedule Windows to open and refresh the Excel workbook
Check refresh completion: The script disables background refresh for supported OLE DB and ODBC connections and waits for pending asynchronous queries, but not every connection type is covered. Verify that all of your queries finish before the workbook is saved; a successful task result alone does not prove the data is current.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceCompatible with common Microsoft Excel formats, including XLSX and XLSReview and work with workbook data in WPS SpreadsheetFree and lightweight for everyday office tasks
microsoft office alternative - wps office

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.