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.
Answer
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>