Excel vba refresh wait

Solution 1:

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.

Solution 2:

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>

Solution 3:

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.

Solution 4:

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

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

enter image description here

Excel vba refresh wait

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Related

Recent Posts