ActiveWorkbook.Connections("x").Refresh完成时要执行的代码 [英] Code to execute when ActiveWorkbook.Connections("x").Refresh is finished

问题描述

从外部来源选择数据
我有一个数据连接，该数据连接使用select查询从SQL服务器使用vba代码将数据检索到Excel工作表中，如下所示:

select data from external source
I have a data connection that retreives data using a select query from SQL-server into an Excel sheet using vba code like this:

With ActiveWorkbook.Connections("x"). _
    OLEDBConnection
    .BackgroundQuery = True
    .CommandText = Array( _
    "SELECT ... FROM ...
...
ActiveWorkbook.Connections("x").Refresh

与导入的数据链接的数据透视表也需要刷新
但是据我所知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").Refresh runs asynchonious and I want to execute code that runs after the refresh has finished, so that I can run this code:

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

，但仅当读入数据时
我怎么知道刷新完成后何时获取所有数据?
我该怎么做才能在刷新完成后仅运行UpdatePivot子目，而不必求助于sleep骇客.

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 UpdatePivot sub after the Refresh is complete without resorting to sleep hacks.

P.S.有时查询会很快(<1秒)，有时会很慢(> 30秒)，这取决于我选择的确切数据，这是动态的.

推荐答案

这不是一个出色的解决方案，但是您可以通过设置

It's not a brilliant solution, but you could make ActiveWorkbook.Connections("x").Refresh run synchronously by setting

.BackgroundQuery = False

另一个更复杂的解决方案是通过检查

Another more complex solution would be to poll the status of the connection by checking the .Refreshing property inside a loop construct.

这篇关于ActiveWorkbook.Connections("x").Refresh完成时要执行的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！