Excel vba刷新等待 [英] Excel vba refresh wait
问题描述
我正在创建一些代码，我可以点击一个按钮，它将刷新我在该表上的查询表。
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.
我不想等待5秒钟，但是令人耳目一新的时期，所以我不会等太久或太短，这取决于互联网速度等。
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.
我该怎么做？
编辑：
简单代码：
ActiveWorkbook.RefreshAll
需要延迟或等待代码，直到所有的刷新完成...然后
Here I need the delay or waiting code till all the refreshing is finished... Then
MsgBox("The Refreshing is Completed!")
这方面的东西。但是，在实际完成之前，msgbox不能说msgbox ....有时根据互联网速度刷新需要更短或更长时间，所以我希望它成为一个实际刷新时间的变量。
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.
推荐答案
在Web查询的外部数据范围属性中，您将有一个复选框，其中显示启用后台刷新应该取消选中以达到预期的效果。
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.
看看这个页面的底部： http://www.mrexcel.com/tip103.shtml 图片
Have a look at the bottom of this page: http://www.mrexcel.com/tip103.shtml for pictures
编辑：
以下是两个显示所需效果的宏：
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
执行AddWebquery添加查询，然后执行TestRefreshing来测试效果。您可以将 .BackgroundQuery = False 更改为 True 以使错误的结果。
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.
具有10秒睡眠的测试页：
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>
这篇关于Excel vba刷新等待的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
