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

Question

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.

score 2 · Accepted Answer · answered May 12 '11 at 11:49

2

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.

answered May 12 '11 at 11:49

Ed Harper

21,127
4
54
80

Need an example of .refreshing property. – Hans Schulze May 16 '20 at 01:49

score 1 · Answer 2 · edited Oct 11 '12 at 05:52

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

score 0 · Answer 3 · edited Apr 28 '21 at 17:19

0

This worked for me:

Sheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

edited Apr 28 '21 at 17:19

Tomerikoo

18,379
16
47
61

answered Apr 28 '21 at 17:01

Keith Swerling

136
1
6

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

3 Answers3

Linked