Forum Discussion

chey123's avatar
chey123
Copper Contributor
Apr 11, 2021

VBA Wait for Query Refresh

Hi Excel Community, 

I am having issues with a detailed macro that requires multiple queries to be refreshed several times throughout the running of the VBA code to allow the user to visualise updates that are being sent to the company SQL database. The issue occurs where the code continues to run even while the queries are still refreshing, meaning that the code is completing calculations within the workbook based on the old data within the query tables rather than the new data that was just sent to the SQL database through previous steps of the same macro. 

I have done a fair bit of trialling based on other discussions and forums around the same issue but none have worked so far (i.e. Query Background Refresh is greyed out/unticked already, trialled using 

Application.CalculateUntilAsyncQueriesDone, inserting a pause/wait action) 
Note that there also seems to be an issue where query refresh speeds differ for users depending on which excel version they have. The issue was not obvious during trialling on my Windows device as I have the latest MS Excel, but for users with slightly older accounts the code encounters an error due to serious hold ups from the query refresh. 
 
Ultimately what I am looking for, is to be able to identify within the code that the refresh of either a specific query or all queries has in fact completed a refresh before continuing with calculations immediately after. Is there something that can call the exact time that a query refresh was completed?

 

developer
excel
Formulas and Functions
Macros and VBA
office 365

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 13, 2021
    Impossible to answer without seeing at least some relevant bits of your code I'm afraid.
    You don't mention what type of query you are talking about, e.g. is this through the legacy query wizard, PowerQuery, VBA, some other connection? A QueryTable object has a Refreshing property which should tell you if it is -well- refreshing.

Resources