Refresh Power Query from VBA Before Continuing a Macro in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets VBA macro to refresh externally sourced Power Query data and wait until the refresh fully completes before dependent code continues.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VBA macro must refresh Power Query data from an external source and then continue processing, but the refresh is asynchronous.
- Observed behavior
- The query refresh does not finish during the macro run, so subsequent macro statements process stale data or fail before the query has updated.
Open the VBA editor in WPS Spreadsheets and confirm that the workbook has the Power Query connection you want to refresh. Check the external data source credentials and permissions so the refresh can complete without a hidden prompt during the macro.
RefreshAll and wait for asynchronous queries with CalculateUntilAsyncQueriesDone
Use this method when the macro must wait for all workbook Power Query refreshes to finish before continuing.
By default, Power Query refreshes started from VBA can run asynchronously. RefreshAll returns immediately, so Application.CalculateUntilAsyncQueriesDone is used to block the macro until the pending refresh work is complete.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor. In the Project Explorer, open the module that contains the macro you need to update.
Place ThisWorkbook.RefreshAll followed by Application.CalculateUntilAsyncQueriesDone immediately before the macro lines that depend on refreshed Power Query data.
Run the macro from the VBA editor Run menu or from the Developer tab > Macros in WPS Spreadsheets. The macro should pause until the Power Query refresh completes, then continue with current data.
Set the Power Query connection BackgroundQuery property to False
Use this when you want one specific query to refresh synchronously instead of waiting for all asynchronous queries.
Refresh Power Query from VBA Before Continuing a Macro in WPS Spreadsheets
WPS Spreadsheets can run VBA macros that refresh Power Query data and wait for the refresh to finish. Use RefreshAll with CalculateUntilAsyncQueriesDone or disable background refresh on the connection before continuing with dependent code.
- 1. Open the VBA editor: In WPS Spreadsheets, press Alt+F11 to open the VBA editor and open the module that contains your macro.
- 2. Add the refresh and wait commands: Insert ThisWorkbook.RefreshAll and Application.CalculateUntilAsyncQueriesDone before the dependent VBA code.
- 3. Alternatively disable background refresh: For a specific connection, set its BackgroundQuery property to False and then refresh that connection.
- 4. Run the macro: Run the macro from the VBA editor Run menu or the Developer tab to confirm that Power Query data is updated before the next steps.
Frequently Asked Questions
Why does my VBA macro continue before the Power Query refresh is finished?
Power Query refreshes are asynchronous by default. In WPS Spreadsheets VBA, call ThisWorkbook.RefreshAll and then Application.CalculateUntilAsyncQueriesDone before dependent code, or disable background refresh for the connection.
How do I make only one Power Query connection refresh synchronously?
Open the Data tab > Connections, identify the connection name, and in VBA set its BackgroundQuery property to False before calling the Refresh method for that connection.
Can I automatically run the next procedure after a refresh completes?
Yes. You can use the QueryTable AfterRefresh event for a query table to call the next macro after the refresh finishes, or perform the refresh synchronously so the next line runs only after the data has loaded.
Do Excel and WPS Spreadsheets support the same VBA Power Query refresh approach?
The core commands such as RefreshAll and CalculateUntilAsyncQueriesDone are compatible with common Excel VBA workflow. If a specific WPS version does not expose a method, use the connection's BackgroundQuery property or a refresh-complete event instead.
