the ultimate refresh-all script?

[acol]

Hi all,
Looking for this for a long time, but never found a definite and all-round 'one-button' vba script.
Maybe someone can pull this off and make a lot of people happy with this; unfortunately I'm unable to pull it of myself.
So the question:

I want 1 script that, for the active workbook:
* updates ALL data connections (differents tabs), and ALL pivots (with different data sources) in that workbook.
and if possible doing the refreshings sequential; for running them all at once (as 'refresh all' does), can sometimes resort in errors...
('refresh all' in a pivot only updates all pivots linked to the same data-source, no other ones)

so in short; something like this:
- check for all data connection in the workbook
- refresh the first data connection, wait until its finished, and then refresh all dependent pivots of that connection in the workbook (spread over multiple tabs)
- refresh the second data connection, wait until its finished, and then refresh all dependent pivots of that connection in the workbook (spread over multiple tabs)
- etc. (do this for all subsequent found data connections)

Can this be achieved?
I would very much appreciate having that macro available.

And to all of you; my best wishes for a healthy, peaceful and excel enriching 2023!

Kr
AC

 

[sijpie]

Does this do the trick? (untested code)

Sub RefreshAllConPiv()
    Dim wbcConn As WorkbookConnection
    Dim ptPivT As PivotTable
    Dim wsWS As Worksheet
    
    ' refresh each data connection in turn, waiting for it to finish
    For Each wbcConn In ThisWorkbook.Connections
        wbcConn.OLEDBConnection.BackgroundQuery = False
        wbcConn.Refresh
    Next wbcConn
    
    ' refresh all pivot tables
    For Each wsWS In ThisWorkbook.Worksheets
        For Each ptPivT In wsWS.PivotTables
            ptPivT.RefreshTable
        Next ptPivT
    Next wsWS
End Sub