Update Power query and tables based on power query by VBA (1 Viewer)

[zezo2021]

Hello friends

I use this code to refresh
Power query and table based on power query and pivot table posed on the table
but not work
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Connections("Query - PROJECT").Refresh
Sheets("Summary Data").ListObjects("PROJECT_1").QueryTable.Refresh BackgroundQuery:=False
ThisWorkbook.RefreshAll



I need to update
(1) Power query
(2) Table (based on power query)
(3) Pivot Table (based on Table)

By code

 

[zezo2021]

up

 

[arnelgp]

' https://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/
Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)

    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn

End Sub

if you want to Refresh the tables/Power Query when you open the workbook,
add code to the Workbook_Open event:

Private Sub Workbook_Open()
    Call UpdatePowerQueries
End Sub

 

[zezo2021]

Thank you

Power query is updated (good)

I need also to update the pivot table based on the power query (table)
is this possible

for example
piviot1.refresh

 

[arnelgp]

there is a sample here for the syntax:
VBA Refresh of Pivot Table — Excel Dashboards VBA (thesmallman.com)

 

[zezo2021]

Thank you so much
the 2 pieces of code worked

but why I should press the macro twice to update the power query and pivot table

 

[arnelgp]

but why I should press the macro twice to update the power query and pivot table

why should you?

 

[zezo2021]

never update from the first click