Reputation: 1131

How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

I have a subroutine that calls ActiveWorkbook.RefreshAll to bring new data in from an XML source on a website, 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.

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.

According to http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html, XML connections do 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.

This code is the macro that is recorded when I do this via the UI (including unchecking the "Enable background refresh" in the table properties):

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.

How can I force my subsequent code to wait until RefreshAll finishes?

Upvotes: 67

Answers (15)

practicalGuy

Reputation: 1328

This is probably caused by background refresh.

To change the setting, go to Connections and select Properties. On the Usage tab, uncheck the "Enable background refresh" checkbox to disable background refresh.

Upvotes: 27

user3142056

Reputation: 347

Here is a trick that has worked for me when some lines of VBA code have trouble executing because preceding lines haven't completed doing their thing. Put the preceding lines in a Sub. The act of calling the Sub to run those lines may help them finish before subsequent lines are executed. This trick has helped me with timing issues using the Windows clipboard.

Upvotes: 0

Valiante

Reputation: 1226

I had the same issue with an OLEDBConnection connection type, however DoEvents (as suggested in a prior answer) 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 worked:

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.

However, note that because this uses BackgroundQuery, this will not work if you are using an xlConnectionTypeXMLMAP connection, sorry.

Upvotes: 86

Michał Sztubecki

Reputation: 1

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("File_where_you_have_to_do_refresh.xlsx")
Workbooks("File_where_you_have_to_do_refresh.xlsx").RefreshAll

Workbooks.Open("Any_file.xlsx)
'Excell is waiting till Refresh on first file will finish'
Workbooks("Any_file.xlsx).Close False

Workbooks("File_where_you_have_to_do_refresh.xlsx").Save

or use this:

Workbooks("File_where_you_have_to_do_refresh.xlsx").Close True

It's working properly on all my files.

Upvotes: 0

Wayne G. Dunn

Reputation: 4312

Here is a solution from 2010 from the MrExcel.com thread, Fails: ActiveWorkbook.RefreshAll BackgroundQuery:=False, by user p45cal:

Either:
have all the pivotcaches' backgroundquery properties set to False, or
loop through all the workbook's pivotcaches:
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:
For Each pc In ActiveWorkbook.PivotCaches
    originalBGStatus = pc.BackgroundQuery
    pc.BackgroundQuery = False
    pc.Refresh
    pc.BackgroundQuery = originalBGStatus
Next
(all untested)

Upvotes: 2

robotik

Reputation: 2007

DISCLAIMER: The code below reportedly caused some crashes! Use with care.

According to this 2015 answer by Ejaz Ahmed, in Excel 2010 and above CalculateUntilAsyncQueriesDone halts macros until refresh is done:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

Upvotes: 12

M. Ramezani

Reputation: 11

For me, "BackgroundQuery:=False" did not work alone But adding a "DoEvents" resolved problem

.QueryTable.Refresh BackgroundQuery:=False
VBA.Interaction.DoEvents

Upvotes: 0

Mike

Reputation: 1

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 "Calculate" (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.

Upvotes: -1

James Koch

Reputation: 1

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

Upvotes: -1

MostFire

Reputation: 37

This worked for me:

ActiveWorkbook.refreshall
ActiveWorkbook.Save

When you save the workbook it's necessary to complete the refresh.

Upvotes: 2

Jennifer

Reputation: 1

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.

Upvotes: -1

rwb

Reputation: 11

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.

Upvotes: 1

tim.s

Reputation: 92

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

Upvotes: 6

mcw

Reputation: 3596

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.

Upvotes: -1

Scott

Reputation: 11

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.

Upvotes: 1

How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

Answers (15)

Related Questions