How to Refresh Excel Queries When a Workbook Is Closed
Question details
The user needs Excel Power Query or external data connections to refresh automatically when the workbook is closed.
- Product
- Microsoft Excel (Power Query and external data connections)
- Device & OS
- Windows
- Scenario
- Scheduled unattended data refresh for a closed Excel workbook.
- Observed behavior
- Excel does not refresh Power Query or external data connections when a workbook is closed, and scheduled refresh settings do not run unless Excel opens the workbook.
Open the workbook in Excel and verify that each connection has the refresh-on-open option enabled. Also confirm that the computer can wake for scheduled tasks and can reach the required files or data sources.
Schedule Excel to Open and Refresh the Workbook with Windows Task Scheduler
Use this solution when you need unattended refresh at a specific time while the workbook is closed.
Excel can run Power Query and connection refreshes only when the workbook is open. Task Scheduler opens the file at the scheduled time, triggers the refresh-on-open setting, and the workbook can then be saved and closed.
Press the Windows key, type Task Scheduler, and open the app.
Click Create Basic Task, enter a name, select a trigger time, and choose Start a program.
Set Program/script to Excel.exe and add the full workbook path in the Add arguments box.
In Excel, go to Data > Queries & Connections, right-click each query, choose Properties, and enable Refresh data when opening file.
Save the workbook and run the scheduled task manually to confirm the refresh finishes correctly.
Enable Refresh on Open for Each Power Query Connection
Use this setting so scheduled tasks or any future workbook open always trigger the data refresh.
Use a VBA Workbook_Open Macro for Advanced Refresh Control
Use this method when you need more granular control, such as refreshing only specific queries and closing the workbook after refresh.
Use WPS Office to Open and Manage Excel Workbooks Without Complex Scheduling
If scheduled Excel refresh workflows are fragile or require extra automation, WPS Office is a free, lightweight alternative that opens and edits .xlsx files with a familiar interface and strong format compatibility.
- 1. Download and install WPS Office: Go to the WPS Office download page and install WPS Spreadsheets.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your queries or data connections.
- 3. Work with compatible data features: Use the Data tab in WPS Spreadsheets to manage supported connections and save the workbook in .xlsx format.
Frequently Asked Questions
Why does Excel not refresh queries while the workbook is closed?
Excel can run Power Query and external data refreshes only when the workbook is open. Closed workbook scheduled refresh settings do not execute because Excel is not running, so the standard workaround is to schedule Excel to open the file with Windows Task Scheduler.
How do I set a query to refresh when the workbook opens?
In Excel, go to Data > Queries & Connections, right-click the connection, choose Properties, and check Refresh data when opening file. Save the workbook so the setting persists.
Can Windows Task Scheduler open an Excel file and refresh it automatically?
Yes. Create a task that starts Excel.exe and passes the workbook path as an argument. At the scheduled time, the task opens the workbook and triggers the refresh-on-open setting.
Does WPS Office support Excel Power Query refresh?
WPS Office can open Excel workbooks and manage compatible data connections in WPS Spreadsheets, but it may not support every Microsoft Power Query transform. It is a lightweight option for viewing, editing, and managing .xlsx files.
Will the scheduled refresh run if the computer is asleep?
No, unless you enable wake timers in Windows Task Scheduler and the relevant power settings. The computer must be on and the required network resources must be accessible.
