Hi,
Please see macro written below. There an additional line of code I can write? To make the code wait for the queries to refresh before refreshing pivots. If so, what could it be? Please advise.
Sub Refresh_PQs_PTables()
' 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
'Step 1: Declare you Variables
Dim ws As Worksheet
Dim pt As PivotTable
'Step 2: Loop through each sheet in workbook
For Each ws In ThisWorkbook.Worksheets
'Step 3: Loop through each pivot table
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
ActiveWorkbook.Worksheets(1).Activate
End Sub
Please see macro written below. There an additional line of code I can write? To make the code wait for the queries to refresh before refreshing pivots. If so, what could it be? Please advise.
Sub Refresh_PQs_PTables()
' 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
'Step 1: Declare you Variables
Dim ws As Worksheet
Dim pt As PivotTable
'Step 2: Loop through each sheet in workbook
For Each ws In ThisWorkbook.Worksheets
'Step 3: Loop through each pivot table
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
ActiveWorkbook.Worksheets(1).Activate
End Sub
