Wait until ActiveWorkbook RefreshAll finishes - VBA

Question

I have a sub that calls on ActiveWorkbook RefreshAll to bring new data in from an XML source  and then performs multiple modifications to it  The problem is that not enough time is given for the RefreshAll command to finish  so the following subs and functions end up not executing correctly  which result in repeated rows not being correctly erased   I have tried using Application Wait and the Sleep function  but they seem to pause the refresh process too  I simply want the rest of the code to wait until the refresh process finishes before executing the rest of the code   Any ideas on how to implement this  Right now I was only able to fix it by not calling on RefreshAll  which gives me the idea of implementing a second flow to be executed afterwards  but that s not a good workaround   Please let me know if any of this wasn t clear  Thanks  EDIT So I tried a few suggestions from the posts below  and this is what I was able to come up with  Doing a  record macro  and then UNCHECKING the  Enable background refresh  in the table properties did not result in anything  I did a refresh as well afterwards  This was the result of the recorded macro   With ActiveWorkbook Connections  XMLTable            Name    XMLTable           Description      End With ActiveWorkbook Connections  XMLTable   refresh   The class ActiveWorkbook Connections does NOT have a BackgroundQuery option so that I can set it to False  Any ideas   Just to be clear  This is an XML file hosted on a website which Excel goes and imports into a table  I then call that data into a pivot and other things  The goal here is to allow the import process from the website to the table to finish BEFORE executing any other commands  Thanks  EDIT2  After a little more research  I have found this page  http   www mrexcel com forum excel-questions 564959-execute-code-after-data-connection-refresh-finished html It appears that an XML type of connection does not have a BackgroundQuery boolean  That option is only available for ODBC and OLEDB connections  which are types xlConnectionTypeODBC and xlConnectionTypeOLEDB  respectively  The XML connection I am using is of type xlConnectionTypeXMLMAP which does not have a BackgroundQuery option  Does anyone have any idea on where to go from here  The only solution I have in mind right now is to make two seperate macro buttons on the excel sheet  one for refreshing and one for data modification  but I d rather keep that option to the very last

User · Answer

For Microsoft Query you can go into Connections --> Properties and untick "Enable background refresh".

This will stop anything happening while the refresh is taking place. I needed to refresh data upon entry and then run a userform on the refreshed data, and this method worked perfectly for me.

User · Answer

I tried a couple of those suggestions above  the best solution for me was to disable backgroundquery for each connection    With ActiveWorkbook Connections  Query - DL 3   OLEDBConnection      BackgroundQuery   False     End With

User · Answer

I had the same issue  however DoEvents didn t help me as my data connections had background-refresh enabled   Instead  using Wayne G  Dunn s answer as a jumping-off point  I created the following solution  which works just fine for me   Sub Refresh All Data Connections        For Each objConnection In ThisWorkbook Connections          Get current background-refresh value         bBackground   objConnection OLEDBConnection BackgroundQuery           Temporarily disable background-refresh         objConnection OLEDBConnection BackgroundQuery   False           Refresh this connection         objConnection Refresh           Set background-refresh value back to original value         objConnection OLEDBConnection BackgroundQuery   bBackground     Next      MsgBox  Finished refreshing all data connections   End Sub   The MsgBox is for testing only and can be removed once you re happy the code waits   Also  I prefer ThisWorkbook to ActiveWorkbook as I know it will target the workbook where the code resides  just in case focus changes   Nine times out of ten this won t matter  but I like to err on the side of caution   EDIT  Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option  sorry   I ll leave the above for anyone  like me  looking for a way to refresh OLEDBConnection types

User · Answer

What I ve done to solve this problem is save the workbook  This forces it to refresh before closing   The same approach works for copying many formulas before performing the next operation

User · Answer

You must turn off  background refresh  for all queries   If background refresh is on  Excel works ahead while the refresh occurs and you have problems   Data   Connections   Properties    uncheck  enable background refresh

User · Answer

Though  Wayne G  Dunn has given in code  Here is the place when you don t want to code  And uncheck to disable the background refresh

User · Answer

If you re not married to using Excel Web Query  you might try opening the URL as a separate Workbook instead   Going that route lets you work on the resulting data once the web request completes  just like if you turn off  Enable background refresh      The nice thing is though  Excel displays a progress bar during the request  instead of just freezing up   showing a load message in the destination cell   See my answer on this question  How can I post-process the data from an Excel web query when the query is complete   The tradeoff of that approach is you have to manage processing the data you get back yourself - Excel won t put it in a given destination for you   We ended up going this route after we tried something pretty similar to what you seem to have been doing

User · Answer

Try executing   ActiveSheet Calculate   I use it in a worksheet in which control buttons change values of a dataset  On each click  Excel runs through this command and the graph updates immediately

User · Answer

DISCLAIMER  The code below reportedly casued some crashes  Use with care       according to THIS answer in Excel 2010 and above CalculateUntilAsyncQueriesDone halts macros until refresh is done  ThisWorkbook RefreshAll  Application CalculateUntilAsyncQueriesDone

User · Answer

I know  that maybe it sounds stuppid  but perhaps it can be the best and the easiest solution  You have to create additional Excel file  It can be even empty  Or you can use any other existing Excel file from your directories   Start  Workbooks Open  quot File where you have to do refresh xlsx quot   Workbooks  quot File where you have to do refresh xlsx quot   RefreshAll  Workbooks Open  quot Any file xlsx   Excell is waiting till Refresh on first file will finish  Workbooks  quot Any file xlsx  Close False  Workbooks  quot File where you have to do refresh xlsx quot   Save  or use this  Workbooks  quot File where you have to do refresh xlsx quot   Close True  It s working properly on all my files

User · Answer

For me   quot BackgroundQuery  False quot  did not work alone But adding a  quot DoEvents quot  resolved problem  QueryTable Refresh BackgroundQuery  False VBA Interaction DoEvents

User · Answer

This worked for me   ActiveWorkbook refreshall ActiveWorkbook Save   When you save the workbook it s necessary to complete the refresh

User · Answer

I was having this same problem  and tried all the above solutions with no success   I finally solved the problem by deleting the entire query and creating a new one    The new one had the exact same settings as the one that didn t work  literally the same query definition as I simply copied the old one    I have no idea why this solved the problem  but it did

User · Answer

I have had a similar requirement  After a lot of testing I found a simple but not very elegant solution  not sure if it will work for you      After my macro refresh s the data that Excel is getting  I added into my macro the line  quot Calculate quot   normally used to recalculate the workbook if you have set calculation to manual   While I don t need to do do this  it appears by adding this in  Excel waits while the data is refreshed before continuing with the rest of my macro

User · Answer

Here is a solution found at http   www mrexcel com forum excel-questions 510011-fails-activeworkbook-refreshall-backgroundquery- 3Dfalse html   Either have all the pivotcaches  backgroundquery properties set to False  or loop through all the workbook s pivotcaches   Code      For Each pc In ActiveWorkbook PivotCaches        pc BackgroundQuery   False        pc Refresh     Next    this will leave all pivotcaches backgroundquery properties as false  You could retain each one s settings with   Code   For Each pc In ActiveWorkbook PivotCaches   originalBGStatus   pc BackgroundQuery   pc BackgroundQuery   False   pc Refresh   pc BackgroundQuery   originalBGStatus Next

User · Answer

This may not be ideal  but try using  Application OnTime  to pause execution of the remaining code until enough time has elapsed to assure that all refresh processes have finished   What if the last table in your refresh list were a faux table consisting of only a flag to indicate that the refresh is complete  This table would be deleted at the beginning of the procedure  then  using  Application OnTime   a Sub would run every 15 seconds or so checking to see if the faux table had been populated  If populated  cease the  Application OnTime  checker and proceed with the rest of your procedure   A little wonky  but it should work

[vba] Wait until ActiveWorkbook.RefreshAll finishes - VBA

Examples related to vba

Examples related to excel