• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to defer layout update for a PowerPivot Pivot Table

V

Vivek D

Member
I am trying to change a few measures and row fields for a pivot table on a button click. I've got this working but it is slow as the pivot table seems to refresh after addition of each field and measure.
Manually I can check the "Defer Layout Update" box in the Field List and do multiple changes before updating the layout.

Setting ManualUpdate to False in VBA though doesn't seem to have any impact.
pt.ManualUpdate = False

Is this a PowerPivot limitation?

Is there any other way to speed up this process?
 
The issue here is not ManualUpdate, as connection is still querying when change is made. There is feature to disable refresh after every minor update, but that's only available after release 0.8.3 in Excel 2013.

Defer Layout Update I believe is not accessible through VBA.

There's a lot missing on VBA side of PowerPivot (especially for 2010), due to various reasons...

If I recall, you use 2010 right?
 
I have both 2010 and 2013 but since most users are still on 2010 I build reports/dashboards using 2010.

I guess, this is yet another reason to get everyone to switch to 2013.

Thanks for the details.
 
You must log in or register to reply here.
Back
Top