ManualUpdate on Excel PivotTable

Question

I'm attemping to update an Excel 2007 pivot table in VSTO (C#) and would like to ensure that the pivot table doesn't get updated until all of my edits are done. So there's a property on the PivotTable class, ManualUpdate, that apparently does exactly what I want. Unfortunately it appears that often times when I assign it the value "true" the value doesn't change. Additionally, I've also seen on other occassions where it will change from "true" to "false" by itself.

BTW, I'm working with an OLAP cube. Does anyone know what the issue/conditions I need to consider with PivotTable.ManualUpdate?

Thanks!

Answer 1

I have the same problem. The pivot tables manualupdate property always resets itself to False wheneever you access any object in the pivottable. This is very annoying. I've googled alld ay but it seems that very few people seem to have this problem.

Answer 2

I have the same problem as well, and it is very annoying. I initially encountered the problem working with an OLAP cube too, but can reproduce with a simple PivotTable and VBA code:

-enter "a" in A1 and "1" in A2 -select A1:A2 and insert a PivotTable -run the following VBA:

Sub ManualUpdateProblem()
    Dim pt As PivotTable
    Set pt = ActiveCell.PivotTable

    MsgBox pt.ManualUpdate
    pt.ManualUpdate = True
    MsgBox pt.ManualUpdate  'still False!!!  :(

    pt.PivotFields("a").Orientation = xlRowField    'executes immediately since ManualUpdate set was not effective

    pt.ManualUpdate = False
End Sub