All Query Tables-wait till refreshed

[syswizard]

I need to run all query tables with connections in the background, but suspend any further VBA processing until have completed.
Should I just create a module-level Array of QueryTable objects upon Workbook open, Do a refresh on each, and then:
Do

bDone = GetAllPivotsProcessStatus()
DoEvents

Loop Until bDone

GetAllPivotsProcessStatus just loops thru all pivot tables checking their Refreshing property.
If all are False, then return True.

Is there a better way ?

[HansV]

Can you refresh the QueryTables with BackgroundQuery:=False as argument? Or does that not apply to your connections?

[syswizard]

Well that's the problem Hans. It was my understanding that VBA processing continues when query tables are running in the background. I need to assure all queries have completed before doing further processing.
This has always been a gray area for me.....and I've noticed most developers seem to set the BackgroundQuery to False.

[HansV]

I meant BackgroundQuery:=False of course! Sorry about that mistake.

According to the help for the BackgroundQuery argument of QueryTable.Refresh: "False to return control to the procedure only after all data has been fetched to the worksheet."

[syswizard]

I think for executing stored procedures in Oracle, SQL Server, etc. one should have the BackgrounQuery set to True....no ?
In that manner, they can execute concurrently and return results much faster.
But, as I mentioned, one must know when the last one has completed.

[HansV]

I don't know of a better method than the one in your first post in this thread.

[Jan Karel Pieterse]

Querytables have events which you can use for this goal.

Insert a class module and name it clsQT.
Have this code in the class:

Code: Select all

Option Explicit

Public WithEvents QT As QueryTable

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
    RefreshDone QT
End Sub

In a normal module enter something like:

Code: Select all

Option Explicit

Dim mcQTEvents As Collection
Dim mlQTCount As Long
Dim mlRefreshed As Long

Sub RefreshAndAct()
    Dim cQTEvent As clsQT
    Dim oSh As Worksheet
    Dim oQT As QueryTable
    Dim oLo As ListObject
    Set mcQTEvents = New Collection
    mlRefreshed = 0
    mlQTCount = 0
    For Each oSh In Worksheets
        For Each oQT In oSh.QueryTables
            mlQTCount = mlQTCount + 1
            Set cQTEvent = New clsQT
            Set cQTEvent.QT = oQT
            mcQTEvents.Add cQTEvent
            oQT.Refresh
        Next
        'As of Excel 2007, Querytables may or may not be in a listobject
        For Each oLo In oSh.ListObjects
            If Not oLo.QueryTable Is Nothing Then
                mlQTCount = mlQTCount + 1
                Set cQTEvent = New clsQT
                Set cQTEvent.QT = oQT
                mcQTEvents.Add cQTEvent
                oQT.Refresh
            End If
        Next
    Next
End Sub

Sub RefreshDone(oQT As QueryTable)
    MsgBox oQT.Name & " is done refreshing"
    'Keep score how many querytables are done
    mlRefreshed = mlRefreshed + 1
    If mlRefreshed = mlQTCount Then
        'we're done refreshing!
        'Now call whatever macro you want to run after the refresh
        
        'Tidy up
        Set mcQTEvents = noting
        mlRefreshed = 0
        mlQTCount = 0
    End If
End Sub

[Jan Karel Pieterse]

NB: I did not test the code...

[syswizard]

NB: I did not test the code...

Nonetheless....very elegant. That is probably the better way to handle this: register each query table at the workbook level and capture the critcal event.
My method ties-up the local workstation in a Do-Loop until all query tables have refreshed.