Excel vba 刷新等待
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8925403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vba refresh wait
提问by dave123
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.
我不想只等待 5 秒，而是等待刷新期，这样我就不会等待太长或太短，具体取决于互联网速度等。
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.
那个方向的东西。但是它不能在实际完成之前说 msgbox ......有时根据互联网速度刷新需要更短或更长的时间，所以我希望它是实际刷新时间的变量。
回答by DKSan
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.shtmlfor pictures
看看这个页面的底部：http: //www.mrexcel.com/tip103.shtml图片
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"))
.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 = Falseto Trueto have the wrong result.
执行AddWebquery添加Query，然后执行TestRefreshing测试效果。您可以更改行.BackgroundQuery = False到True有错误的结果。
Testpage with 10 second sleep:
具有 10 秒睡眠的测试页：
<!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>
回答by Ejaz Ahmed
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.
我正在使用 PowerPivot 模型，我想在保存和关闭模型之前刷新数据。但是，excel只是在刷新完成之前关闭了模型，并且模型在打开时恢复刷新。
Adding the following line right after the RefreshAll method, did the trick:
在 RefreshAll 方法之后添加以下行，就成功了：
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.
请注意，我使用的是 Excel 2010，我不确定此方法是否在旧版本中可用。
回答by RemarkLima
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 backgroundQueryproperty is definately falsebefore calling the refresh.
像这样，我们能够在调用刷新之前确保所有连接backgroundQuery属性都是明确false的。
回答by Ashish Chandra
Uncheck the "Enable Background Refresh" in the Data -> Connection -> Properties
在数据 -> 连接 -> 属性中取消选中“启用后台刷新”
This will disable refresh in background and wait for Refresh to complete.
这将在后台禁用刷新并等待刷新完成。
回答by ggv
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.
当您按照以下方式放置时，BackgroundQuery = False 似乎不会将 BackgroundQuery 属性更改为 False。
Selection.ListObject.QueryTable.Refresh BackgroundQuery = False ' doesn't work
回答by mcw
Another way to go would be to use the Workbooks.Open command to load the URL as a separate workbook instead.
另一种方法是使用 Workbooks.Open 命令将 URL 作为单独的工作簿加载。
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.
这使您可以在呼叫完成后立即完全访问来自 Web 请求的数据。此外，Excel 在加载时会显示一个进度条，而不是像 Web 查询那样冻结。
See my answer on this question: How can I post-process the data from an Excel web query when the query is complete?
请参阅我对这个问题的回答：查询完成后，如何对 Excel Web 查询中的数据进行后处理？
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.
这种方法的权衡是您必须自己管理处理您取回的数据 - Excel 不会为您将其放在给定的目的地。
We ended up going this route after we tried something pretty similar to what you seem to have been doing.
在我们尝试了与您似乎一直在做的非常相似的事情之后，我们最终走上了这条路线。
回答by DDuffy
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.
我知道这是一个老问题，但这对我有用。也适用于公式计算时的等待。
回答by Krishna
If you want to make your script wait in vba you have to use sleep. But sleep sometimes won't work in Excel vba.
如果你想让你的脚本在 vba 中等待，你必须使用睡眠。但是 sleep 有时在 Excel vba 中不起作用。
http://99students.com/macro-sleep-vba/
http://99students.com/macro-sleep-vba/
Instead of that try with
而不是尝试
Application.Wait (Now + TimeValue("0:01:00"))
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
回答by A.G.Johnson
'From [email protected] 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.
'来自 [email protected] 2014-08-11 '这是一个简单的版本，可以让您完全控制。'不要使用 RefreshAll，而是创建以下子例程： '从您的 Excl VBA 中调用您想要执行的例程，'在它完成之前不会发生任何其他事情。'另一个好处是它不会刷新任何数据透视表，因此它们不会干扰，'并且如果您有依赖刷新数据的数据透视表，您可以在查询刷新完成后为您的数据透视表运行类似的刷新'。
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
