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.

Use `DoEvents` after the `Activeworkbook.RefreshAll` statement. — L42, Feb 28 '14 at 00:32
I have never used it before. Can you show me an example please? — Mo2, Feb 28 '14 at 00:38
Just add `DoEvents` statement right after your `Activeworkbook.RefreshAll`. To help you visualize, I'll post is as answer. — L42, Feb 28 '14 at 06:42
Have you tried actually separating your code? What happens? Does it do what you want? I have no way to test the same query set up so i cannot simulate. If it does what you want separately, there is no reason for it not to work together. — L42, Mar 01 '14 at 09:44
I apologize, it seems that I missed something. Marked as answer. Thanks to all those that responded. — Mo2, Mar 04 '14 at 07:32

score 72 · Answer 1 · edited May 23 '17 at 12:10

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.

This worked for me, but using `ODBCConnection` instead of `OLEDB`. — Dan, Nov 20 '14 at 16:05
This didn't worked for me says run time error 1004 Application defined or object defined error — Stupid_Intern, Apr 04 '17 at 19:18
I too was getting the run time error 1004, but I was trying the suggestion of changing OLEDBConnection to ODBCConnection per @Dan, since I am using an ODBC DSN. That produced the 1004 error though. Then I realized that when you create ODBC connections in more modern versions of Excel, it actually saves it as an OLEDBConnection. So I used the original code and it worked just fine. — David Beckman, Jan 25 '22 at 01:02

subro · Answer 2 · 2020-09-02T08:48:09.727

21

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.

edited Sep 02 '20 at 08:48

answered Jun 23 '16 at 12:45

subro

933
2
16
27

1

Sorry to resurrect this, I'm having the same issue - where is that box found? – jfgoodhew1 Aug 28 '20 at 09:06
2

go to, Connections --> Properties --> uncheck Enable background refresh – subro Sep 02 '20 at 08:47

robotik · Answer 3 · 2018-01-25T16:11:27.700

9

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

edited Jan 25 '18 at 16:11

answered Jul 26 '16 at 17:08

robotik

1,672
1
19
23

This caused my excel to crash. AsyncQueries are usually related to refreshes on cube data. – rohrl77 Nov 15 '16 at 13:16
Same here excel crashed when I ran this – Stupid_Intern Apr 04 '17 at 19:22
i'm really sorry about the crashes. works fine for me, and not on cube data – robotik Jan 25 '18 at 16:19
So far, I've only used this for non-cube PowerQuery refreshes from a SQL server. It worked fine as long as the queries were **not** set to refresh in the background. (w/ excel 2016). Otherwise, it would randomly crash. – Mistella Feb 17 '18 at 17:48
Works well for me too! Thanks! – Michthan Nov 22 '18 at 11:55

score 7 · Answer 4 · answered Aug 18 '16 at 00:14

7

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

answered Aug 18 '16 at 00:14

tim.s

71
1
2

I was trying to copy/paste data from one tab to another after a data refresh. The paste kept coming up blank, but after disabling "background refresh" it's working perfectly. Thanks @tim.s – CowboyBebop Jan 17 '18 at 00:43
This might have other implications that go with it. But for what I was trying to accomplish, it works great. Thanks – peege May 04 '22 at 18:05

score 1 · Answer 5 · answered Feb 28 '14 at 04:01

1

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

answered Feb 28 '14 at 04:01

Wayne G. Dunn

4,218
1
11
24

Is there something that I have to enable in order to use this? I'm getting a 1004 error at the line `pc.BackgroundQuery = False` I should also mention that the data is not being imported directly into a pivot. It's been imported into a table, which then a pivot table feeds off of. Does this still work for this case? – Mo2 Feb 28 '14 at 17:29
Now I should mention that when I go to the Data tab and then click on Connections and then pick my XML connection (hosted on website) and click properties, I have the "Enable background refresh" UNCHECKED. Is that the same thing as BackgroundQuery? – Mo2 Feb 28 '14 at 17:57
1

You have the correct setting - you do not want the refresh to run in the background. Re 'not directly into a pivot... feeds off..' I'm guessing that still counts. Also, please take a look at this http://stackoverflow.com/questions/19916824/excel-2013-1004-runtime-error-refresh-query-table-backgroundquery-false – Wayne G. Dunn Feb 28 '14 at 18:17
Mine is a web query, nothing related to SQL. It basically goes to a website where an XML is hosted and imports it into a table. Nothing more or less. The problem is that it doesn't wait for the import to finish. I tried recording a macro when doing the refresh AND unchecking the "Enable background refresh" and the results were interesting. There is no BackgroundQuery option for the subclass I'm using. Read the OP for an update. – Mo2 Feb 28 '14 at 18:56

score 1 · Answer 6 · edited Apr 06 '19 at 00:06

1

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.

edited Apr 06 '19 at 00:06

Cody Gray

230,875
49
477
553

answered Mar 19 '15 at 13:22

rwb

11
1

score 1 · Answer 7 · answered Jul 22 '15 at 14:55

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.

score 1 · Answer 8 · edited May 30 '19 at 14:43

1

This worked for me:

ActiveWorkbook.refreshall
ActiveWorkbook.Save

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

edited May 30 '19 at 14:43

Alex Myers

5,068
7
21
35

answered May 30 '19 at 14:10

MostFire

19
1

1

this one gives me a popup: "This will cancel a pending data refresh. Continue?" – Henrik Mar 23 '20 at 06:24

score 1 · Answer 9 · answered Oct 18 '21 at 16:42

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. I learned of this trick from https://peltiertech.com/ and it has helped me with timing issues using the Windows clipboard.

score 0 · Answer 10 · edited Apr 06 '19 at 00:06

0

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.

edited Apr 06 '19 at 00:06

Cody Gray

230,875
49
477
553

answered Sep 02 '15 at 22:49

Jennifer

1

score 0 · Answer 11 · edited May 23 '17 at 10:31

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.

score 0 · Answer 12 · edited Jun 28 '19 at 08:06

0

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

edited Jun 28 '19 at 08:06

BDL

19,702
16
49
50

answered Jun 28 '19 at 07:25

James Koch

1

score 0 · Answer 13 · answered Sep 27 '19 at 08:45

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.

score 0 · Answer 14 · edited Dec 08 '20 at 17:31

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.

score 0 · Answer 15 · answered Feb 08 '21 at 17:26

0

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

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

answered Feb 08 '21 at 17:26

M. Ramezani

11
2

score 0 · Answer 16 · edited Feb 24 '21 at 13:42

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

score -1 · Answer 17 · edited Apr 06 '19 at 00:06

-1

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.

edited Apr 06 '19 at 00:06

Cody Gray

230,875
49
477
553

answered Apr 05 '19 at 19:21

user11318842

1

Wait until ActiveWorkbook.RefreshAll finishes - VBA

17 Answers17

Linked

Related