VBA Excel - Refresh Pivot Table/Power Query

Question

I'm trying to refresh a Power Query & then a Pivot Table (running off that data) & I can get it to work when I step through the code using F8 but it will not work when running the macro

Any ideas?

Thanks!!

Sub Test()

Dim WB As Workbook
Dim pt As PivotTable
Set WB = ThisWorkbook
Set pt = ActiveSheet.PivotTables("PivotTable1")

'Refresh Power Queries & Pivot Tables
ThisWorkbook.RefreshAll
WB.Worksheets("By Customer Pivot").Activate
pt.RefreshTable


End Sub

Jenn Jenn · Accepted Answer · 2019-12-01T13:25:41

This is how you can refresh all Power Queries.

Sub RefreshAllPQ_Queries()

    Dim Connection As WorkbookConnection
    Dim lTest As Long

    On Error Resume Next

    For Each cn In ThisWorkbook.Connections

        lTest = InStr(1, cn.OLEDBConnection.Connection, _
                "Provider=Microsoft.Mashup.OleDb.1")

        If Err.Number <> 0 Then

            Err.Clear

            Exit For

        End If

    If lTest > 0 Then cn.Refresh

    Next cn


End Sub

And this is how you can refresh a pivot table.

Sub RefreshPivotTable()


    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strWS As String
    Dim strPT As String

    strWS = "Invoice"
    strPT = "pt_Invoice"
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(strWS)

    ws.PivotTables(strPT).PivotCache.Refresh

    Set ws = Nothing
    Set wb = Nothing
    strWS = vbNullString
    strPT = vbNullString

End Sub

VBA Excel - Refresh Pivot Table/Power Query

1 Answers