vba ActiveWorkbook.Connections("x").Refresh 完成时执行的代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5976400/
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
Code to execute when ActiveWorkbook.Connections("x").Refresh is finished
提问by Johan
select data from external source
I have a data connection that retreives data using a selectquery from SQL-server into an Excel sheet using vba code like this:
从外部源选择数据
我有一个数据连接，它使用select来自 SQL-server的查询将数据检索到使用 vba 代码的 Excel 工作表中，如下所示：
With ActiveWorkbook.Connections("x"). _
OLEDBConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT ... FROM ...
...
ActiveWorkbook.Connections("x").Refresh
linked pivot table to imported data needs to be refreshed as well
However as far as I can tell ActiveWorkbook.Connections("x").Refreshruns asynchonious and I want to execute code that runs after the refresh has finished, so that I can run this code:
链接到导入数据的数据透视表也需要刷新
但是据我所知ActiveWorkbook.Connections("x").Refresh运行是异步的，我想执行在刷新完成后运行的代码，以便我可以运行以下代码：
Private Sub UpdatePivot()
Dim PV As PivotItem
For Each PV In ActiveSheet.PivotTables("PT1").PivotFields("PN").PivotItems
If PV.Name <> "(blank)" Then
PV.Visible = True
Else
PV.Visible = False
End If
Next
End Sub
but only when the data is read in
How do I know when the refresh is done getting all the data?
What do I have to do to only run the UpdatePivotsub after the Refresh is complete without resorting to sleephacks.
但只有在读取数据时
我怎么知道刷新完成后获取所有数据？
我必须做什么才能UpdatePivot在刷新完成后才运行子程序而不诉诸sleep黑客。
P.S. Sometimes the query is fast (<1 sec), sometimes it's slow (> 30 sec) depending on the exact data i'm selecting, which is dynamic.
PS 有时查询很快（<1 秒），有时很慢（> 30 秒）取决于我选择的确切数据，这是动态的。
采纳答案by Ed Harper
It's not a brilliant solution, but you could make ActiveWorkbook.Connections("x").Refreshrun synchronously by setting
这不是一个绝妙的解决方案，但您可以ActiveWorkbook.Connections("x").Refresh通过设置同步运行
.BackgroundQuery = False
Another more complex solution would be to poll the status of the connection by checking the .Refreshingproperty inside a loop construct.
另一个更复杂的解决方案是通过检查.Refreshing循环结构内的属性来轮询连接的状态。
回答by Hank
.BackgroundQuery = Falsewill NOT ensure synchronous execution following the data refresh.
.BackgroundQuery = False不会确保数据刷新后同步执行。
Try it yourself by creating a simple query and in the Worksheet_Change subroutine add code to select a few cells. I can often get 2 commands to fire before the waiting/timeout circle appears.
通过创建一个简单的查询并在 Worksheet_Change 子例程中添加代码来选择几个单元格，自己尝试一下。我经常可以在等待/超时圈出现之前触发 2 个命令。
Thus I cannot determine if the query has returned the right data. I have tried setting a reference cell to the value of a mid query column and checked that the two are equal - unfortunately the Worksheet_Changeevent FIRES TWICE UPON DATA REFRESH!
因此我无法确定查询是否返回了正确的数据。我尝试将参考单元格设置为中间查询列的值并检查两者是否相等 - 不幸的是，Worksheet_Change事件在数据刷新时触发两次！
This is driving me crazy. I just need to go print a chart after successful query refresh.
这真让我抓狂。我只需要在成功查询刷新后打印图表。
