Fix VBA Copying Old Excel Data Before Power Query Refresh Finishes
Question details
The user needs to prevent a VBA macro from executing copy commands before a Power Query data refresh has finished, which currently results in outdated data being copied.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating data extraction or reporting where a VBA macro triggers a Power Query refresh and immediately copies the resulting table.
- Observed behavior
- The macro copies the old table values because the Power Query background refresh is still running when the copy command executes.
Ensure you have saved a backup of your Excel workbook and have access to the Developer tab to edit your VBA macro code.
Disable Background Refresh in Connection Properties
The most reliable way to ensure VBA waits for the query to finish is to turn off background refresh in Excel's connection settings.
Open your Excel workbook and navigate to the 'Data' tab on the ribbon, then click on 'Queries & Connections' to open the side pane.
Right-click your specific Power Query connection in the side pane and select 'Properties' from the context menu.
In the 'Usage' tab of the Connection Properties dialog, uncheck the box that says 'Enable background refresh'.
Click 'OK' to save the changes. Your VBA macro will now naturally pause until the refresh operation completes before moving to the copy commands.
Add a Wait Loop in Your VBA Code
If you must keep background refresh enabled, you can add a loop in your VBA script that checks the query status before proceeding.
Set BackgroundQuery Property to False in VBA
You can programmatically force the query to refresh in the foreground directly within your macro.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter complex macro and data connection issues in Microsoft Excel, consider trying WPS Office. It is a lightweight, highly compatible, and free alternative that supports advanced spreadsheet functions, VBA macros, and seamless data handling.
Frequently Asked Questions
Why does my VBA macro run before the Excel data refresh finishes?
By default, Excel Power Query connections have 'Enable background refresh' turned on. This allows you to continue working in Excel or running code while data loads, meaning VBA will sequentially move to the next line of code without waiting for the data retrieval to finish.
What does DoEvents do in a VBA wait loop?
The DoEvents function temporarily yields execution to the operating system. In a wait loop, it prevents Excel from freezing or throwing a 'Not Responding' error, allowing the application to process the ongoing Power Query background refresh.
Can I prevent background refreshing for multiple queries at once?
Yes. If you are using VBA, you can write a loop that iterates through all QueryTables or ListObjects in your workbook and sets BackgroundQuery:=False for each one prior to executing the refresh method.
