Code to execute when ActiveWorkbook.Connections(“x

问题:

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

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

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. Sometimes the query is fast (<1 sec), sometimes it's slow (> 30 sec) depending on the exact data i'm selecting, which is dynamic.

回答1:

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.

回答2:

.BackgroundQuery = False will NOT ensure synchronous execution following the data refresh.

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.

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_Change event FIRES TWICE UPON DATA REFRESH!

This is driving me crazy. I just need to go print a chart after successful query refresh.