HELP! Need to stop Pivot refreshing with VBA

[alemarquezr]

Hi all,

I have a file connected to a cube and I created the following code to add Pivot Tables to my slicers automatically BUT every time one pivot is added EXCEL is creating "Running OLAP Query".

PROBLEM: I would like to stop that query and refresh my pivots only when all pivots have been added to all my slicers.

I appreciate your support on this, Thanks!

************************************************************************
Sub ConnectAllSlicerPivots()


Dim MySheet As Worksheet
Dim MyPivot As PivotTable
Dim slCaches As SlicerCaches
Dim slCache As SlicerCache
Dim oSht As Worksheet

Set slCaches = ThisWorkbook.SlicerCaches


For Each slCache In slCaches
For Each MySheet In ActiveWorkbook.Worksheets
For Each MyPivot In MySheet.PivotTables
Debug.Print "To Connect:", MyPivot, "in", slCache.Name
slCache.PivotTables.AddPivotTable MyPivot
Next MyPivot
Next MySheet
Next slCache

MsgBox ("Slicers connected: Graphs Updated")



************************************************************************


BR/ AM

 

[Comfy]

Try:

With Application
.EnableEvents = False
End With

'Your code

With Application
.EnableEvents = True
End With

 

[alemarquezr]

Hi Comfy,

It didn't work :S, it seems that pivots force Excel to connect themselves or something but "events" is not freezing the query for each pivot added.

 

[Comfy]

I think I had the wrong setting:

Try:

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With


'Your code


With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

or add this to your code and update all pivots manually:

For Each MyPivot In MySheet.PivotTables
Debug.Print "To Connect:", MyPivot, "in", slCache.Name
MyPivot.ManualUpdate = true
slCache.PivotTables.AddPivotTable MyPivot
Next MyPivot

 

[alemarquezr]

Hi Comfy,

Thanks for your help, with xlCalculationManual you can still see Excel calling "OLAP Query" then if additionally you force manual update for Pivots you still see Excel calling "Running Slicer Operation"

I tried to force my SlicerCache to use the function defined called RequireManualUpdate but I get error :S

Thanks

 

[Comfy]

IT might not be possible:

https://social.msdn.microsoft.com/F...sh-on-slicer-selection-changes?forum=exceldev
Avoid Constant Refreshes in Excel 2013 - PowerPivotPro PowerPivotPro

 

[alemarquezr]

Yes, I read that post too but by trying now some codes I think that if I make the slicer to stop updating (by using RequireManualUpdate) I can get somewhere.

the issue now is that RequireManualUpdate is not working for me...