Forum Discussion
Solved
Pivot Table Will Not Update Properly
I have data created in PowerQuery that is connected to PowerPivot which generates PivotTable in my excel sheets. I have noticed that my pivot tables do not refresh with the most recent data once I: r...
I always had the exact same problem and finally found the solution, which appears to be embarrasingly simple.
Click Change Data Source. After updating my data (obviously) the data range has expanded! Select the data set again and voilà.
Yemisi13, it is a macro file so you need to have Excel open and use the VBA editor to see the code.
I am posting the code here for your convenience.
Sawcy1
Sub Set_Connection_Property_Disable_Background_Refresh()
Dim con As Object
For Each con In ActiveWorkbook.Connections
If con <> "ThisWorkbookDataModel" Then
With con
Debug.Print con.Name
If con.OLEDBConnection.BackgroundQuery = True Then
MsgBox "Background Refresh was on for " & con.Name & ". Turning off now."
End If
Debug.Print con.OLEDBConnection.BackgroundQuery
con.OLEDBConnection.BackgroundQuery = False
Debug.Print con.OLEDBConnection.BackgroundQuery
Debug.Print "-----------------------"
End With
End If
Next con
End Sub
I am posting the code here for your convenience.
Sawcy1
Sub Set_Connection_Property_Disable_Background_Refresh()
Dim con As Object
For Each con In ActiveWorkbook.Connections
If con <> "ThisWorkbookDataModel" Then
With con
Debug.Print con.Name
If con.OLEDBConnection.BackgroundQuery = True Then
MsgBox "Background Refresh was on for " & con.Name & ". Turning off now."
End If
Debug.Print con.OLEDBConnection.BackgroundQuery
con.OLEDBConnection.BackgroundQuery = False
Debug.Print con.OLEDBConnection.BackgroundQuery
Debug.Print "-----------------------"
End With
End If
Next con
End Sub