Excel vba refresh wait

Question

I am creating some code where I can click on a single button and it will refresh the querytables that I have on that sheet.

Now, my problem is that I have more code after the fresh that copies some of the information, but this code is being run right after the refresh has started and the information has not yet been replaced.

I want to create a waiting period for the refresh to complete and then the rest of the code can continue.

I don't want to just wait for 5 seconds but for the refreshing period, so that I am not waiting too long or too short, depending on Internet speed etc.

How can I do this?

Edit:

Simple code:

ActiveWorkbook.RefreshAll

Here I need the delay or waiting code till all the refreshing is finished... Then

MsgBox("The Refreshing is Completed!")

Something in that direction. But it can't say the msgbox before it is actually finished.... Sometimes depending on internet speed the refreshing takes shorter or longer, so I want it to be a variable of the actual refreshing time.

Are you using `querytable.refresh false` to specify a non-background refresh? — Alex K., Jan 19 '12 at 11:48
so far only ActiveWorkbook.RefreshAll but I need a loop to check when finished refreshing. Thats how I imagined it — dave123, Jan 19 '12 at 11:50
You may be able to do it by refreshing them individually' `for i=1 to ActiveWorkbook.querytables.count : ActiveWorkbook.querytables(i).refresh false : next` — Alex K., Jan 19 '12 at 11:54
sadly it doesn't allow a wait for the period of time while refreshing — dave123, Jan 19 '12 at 11:57
Ah, looking at the docs it seems that only works for querytables based on an sql source, are you using web queries instead? — Alex K., Jan 19 '12 at 12:15

score 15 · Answer 1 · answered Nov 06 '14 at 12:59

15

I was working with a PowerPivot model, and I wanted to Refresh the data before I saved and closed the Model. However, excel just closed the model before the refresh was complete, and the model resumed refreshing on opening.

Adding the following line right after the RefreshAll method, did the trick:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

I hope it works for you too.

Make sure that you Disable Events to speed things up.

Note that I am using Excel 2010, I am not sure if this method is available in older versions.

answered Nov 06 '14 at 12:59

Ejaz Ahmed

598
7
13

2

Update: I have noticed that this method sometimes does not work very reliably on the Excel 365 Subscription Version of the Desktop App. Excel just stays unresponsive for long periods of time. Setting the background refresh property to False seems to be the only option. However, if you are working with OLAP connections, you may not be able to do that also, Let me know if any of you are facing similar issues as well. – Ejaz Ahmed May 22 '22 at 05:58
I noticed that later on, there were some stability issues with the 'Application.CalculateUntilAsyncQueriesDone' method. That made me ask this question here on stackoverflow: https://stackoverflow.com/q/62978078/2391873 The answer that I received was: Excel needs to be in Auto Calculate Mode for this to work properly. – Ejaz Ahmed Jun 27 '23 at 11:48
This worked for me. My queries are OData feeds & using Office 365 subscription. – MartyMcfly0033 Aug 14 '23 at 20:11

DKSan · Answer 2 · 2012-01-19T20:16:25.030

In the External Data Range Properties of your Web-Query you have a checkbox saying something like "Enable background refresh" which you should uncheck to achieve the desired effect.

Have a look at the bottom of this page: http://www.mrexcel.com/tip103.shtml for pictures

Edit:

Here are two macros that show the desired effect:

Sub AddWebquery()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://de.selfhtml.org/html/tabellen/anzeige/table_tr_th_td.htm", _
        Destination:=Range("$A$1"))
        .Name = "table_tr_th_td"
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub TestRefreshing()
    Range("A1").Clear
    ActiveWorkbook.RefreshAll
    Debug.Print "Test: " & Range("A1").Value
End Sub

Execute AddWebquery to add the Query, then execute TestRefreshing to test the effect. You can change the line .BackgroundQuery = False to True to have the wrong result.

Testpage with 10 second sleep:

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>SO-Test</title>
    </head>
    <body>
        <?php
        sleep(10);
        ?>
        <table border="1">
            <thead>
                <tr><th>1</th></tr>
            </thead>
            <tbody>
                <tr><td>2</td></tr>
            </tbody>
        </table>
    </body>
</html>

this doesn't allow me to refresh with VBA and wait for it to finish refreshing — dave123, Jan 19 '12 at 14:31
yes it does. Just tested it. Changed in Properties and called RefreshAll with Debug.Print right after it. It always returned the desired value, never empty value — DKSan, Jan 19 '12 at 14:41
This still doesn't allow a waiting period before the next code is run. If I add MsgBox("finished refreshing") after the Debug.Print... then the MsgBox appears before the refreshing is completed. — dave123, Jan 19 '12 at 16:20
Which excelversion are you using? I just created a page which sleeps for 10 seconds and outputs a table afterwards. As expected, my makro stopped for 10 seconds and printed the retuned value in direct console — DKSan, Jan 19 '12 at 20:11
it looks like it works, but if you take a larger website with much more information. and you run the TestRefresh Macro it still doesn't work. isn't there a way of excel knowing its refreshing and you can create a loop. like While refreshing=true do loop until refreshing =False.... somethign in that direction, because this isn't it yet — dave123, Jan 19 '12 at 20:52

score 7 · Answer 3 · answered Oct 12 '12 at 09:01

7

I've just had a similar issue, and we've solved it by the following:

For i = 1 To ActiveWorkbook.Connections.Count
    ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery = False
    'MsgBox ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery
Next

ActiveWorkbook.RefreshAll

Like this we're able to make sure all the connections backgroundQuery property is definately false before calling the refresh.

answered Oct 12 '12 at 09:01

RemarkLima

11,639
7
37
56

This is very bad with multiple queries, because with background refresh these queries can run in parallel. Without background refresh, each query only starts after the previous one finishes. – GSerg Aug 23 '21 at 09:50
@GSerg it depends on your use case. In this instance, it wasn't about speed but reliability, and ensuring that all data was loaded before any reports were generated or analysis performed. – RemarkLima Aug 23 '21 at 10:17

score 2 · Answer 4 · answered May 24 '17 at 04:02

2

Uncheck the "Enable Background Refresh" in the Data -> Connection -> Properties

This will disable refresh in background and wait for Refresh to complete.

answered May 24 '17 at 04:02

Ashish Chandra

381
7
21

score 1 · Answer 5 · answered Feb 15 '16 at 14:48

1

ActiveWorkbook.RefreshAll
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop

I know its an old question, but this worked for me. Also works for waiting while formulas calculate.

answered Feb 15 '16 at 14:48

DDuffy

413
1
4
21

did you activate the workbook first? (I know its a silly question, but the number of times my code hasn't worked because I've forgotten to do something simple is unreal). MyWorkbook.Activate Then .RefreshAll – DDuffy Apr 11 '16 at 10:30
1

Yes, of course. `Application.CalculateUntilAsyncQueriesDone` solved the problem at last. – Przemyslaw Remin Apr 11 '16 at 11:07

score 1 · Answer 6 · answered Nov 27 '18 at 19:25

Try this approach:

With Selection.ListObject.QueryTable
  .BackgroundQuery = False
  .Refresh
End With

When you put it this following way, the BackgroundQuery = False doesn't seem to change the BackgroundQuery property to False.

Selection.ListObject.QueryTable.Refresh BackgroundQuery = False  ' doesn't work

score 0 · Answer 7 · answered Feb 20 '13 at 15:58

If you want to make your script wait in vba you have to use sleep. But sleep sometimes won't work in Excel vba.

http://99students.com/macro-sleep-vba/

Instead of that try with

Application.Wait (Now + TimeValue("0:01:00"))

Sample Code

Sub Setting_Sleep_Without_Sleep_Function()
 MsgBox Now
 Application.Wait DateAdd("s", 10, Now)
 MsgBox Now
End Sub

score 0 · Answer 8 · edited Aug 05 '16 at 02:45

'From A.G.Johnson@Live.com 2014-08-11 'Here's a simple version that will allow you complete control. 'Instead of using RefreshAll, create the following subroutine: 'Call the routine from your Excl VBA wherever you want to execute it, 'and nothing else happens until it is done. 'Another benefit is that it does not refresh any Pivot tables, so they don't interfere, ' and if you have pivots that rely on the refreshed data, you can run a similar refresh ' for your pivots after the query refresh is completed.

sub RefreshQueries()
    dim ws as worksheet
    dim qt as QueryTable
    For each ws in thisworkbook.worksheets
        For each qt in ws.querytables
            qt.refresh
        next qt
    next ws
end sub

score 0 · Answer 9 · edited May 23 '17 at 12:18

Another way to go would be to use the Workbooks.Open command to load the URL as a separate workbook instead.

That gives you full access to the data from the web request right after the call finishes. Plus, Excel shows a progress bar while it loads, instead of freezing up like with a Web Query.

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.

Excel vba refresh wait

9 Answers9

Linked

Related