Refreshing Queries and PivotTables subsequently with VBA

Question

I have an issue that I cannot seem to find a proper solution for. Basically I'm creating an excel dashboard which gets data from specific excel files, based on a file path that the user can choose. Further, some data is pulled from a live connection to a PowerBI Dashboard. This data is then processed in PowerQuery and PowerPivot, after which it is added into 4 different PivotTables. These 4 PivotTables are then processed with VBA to do some data transformation, and finally all added together in one table, which is then added to my data model, and ends up in one PivotTable which is my end result.

My issue here is that I need to update all my queries and connections first, after this I wish to update all my PivotTables, then run my VBA subs to do the data transformation, after which I want to do yet another update on my queries and PivotTables. I have tried several solutions, however none of them seem to be working for me. The issue is that VBA does not wait for all data queries to complete before it moves on to the next line.

If anyone has a smart solution, it would greatly appreciated!

These are some solutions I have tried so far:


Sub Refresh()

Call M_HelpFunctions.OptimizeExcelVBACodeExecution(True)

ThisWorkbook.RefreshAll
Call M_DataTransform.PowerBiData
Call M_DataTransform.MergePrintArrays
ThisWorkbook.RefreshAll

MsgBox "All data has been succesfully refreshed"

Call M_HelpFunctions.OptimizeExcelVBACodeExecution(False)

End Sub

This did not work due to the issues mentioned above.


    Sub Refresh_All_Data_Connections()
        Dim objConnection, bBackground
            For Each objConnection In ThisWorkbook.Connections
                'Get current background-refresh value
                bBackground = objConnection.OLEDBConnection.BackgroundQuery
        
                'Temporarily disable background-refresh
                objConnection.OLEDBConnection.BackgroundQuery = False
        
                'Refresh this connection
                objConnection.refresh
        
                'Set background-refresh value back to original value
                objConnection.OLEDBConnection.BackgroundQuery = bBackground
            Next
    ThisWorkbook.RefreshAll
    End Sub

This does not work since it is not OLEDB Connections, and the "Background refresh" option is greyed out.

Application.Wait (Now + TimeValue("0:00:10"))

This does not work since it also stops my refreshes.

    ' Wait for these queries to be refreshed
    For i = 1 To 60 ' 60 checks, one per second
        If Not ThisWorkbook.Connections("Master Data Model").Refreshing And Not ThisWorkbook.Connections("Development").Refreshing Then Exit For
        Application.Wait (Now + TimeValue("0:00:01"))
        DoEvents
    Next i

This does not work since these are queries and not connections.

Check out [Application.CalculateUntilAsyncQueries](https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculateuntilasyncqueriesdone), also see this [SO Q&A](https://stackoverflow.com/questions/22083668/how-to-wait-until-activeworkbook-refreshall-finishes-before-executing-more-code) — JohnM, Jul 31 '23 at 09:39

score 0 · Answer 1 · answered Aug 22 '23 at 12:03

Find your Query Properties, and set 'Enable background refresh' to False (Microsoft use True as default).

Then in your code you need to call for RefreshAll and wait for the data to load with the DoEvents. If you want your update of data to run on open you can use this in 'ThisWorkbook' Object.

Like this eksample:

Private Sub Workbook_Open()
For Each q In ThisWorkbook.Connections
q.Refresh
DoEvents
Next
End Sub

NOTE: This only updates your data - and not your Pivot reports. But now you control the sequence in code.

Refreshing Queries and PivotTables subsequently with VBA

1 Answers1