Search code examples
sql-serverexcelremote-connectionsvba

Code to execute when ActiveWorkbook.Connections("x").Refresh is finished

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.

Solution

  • 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.