pivottable.ManualUpdate [ = setting ]
True causes RefreshTable to clear data from the pivot table, rather than refreshing it
False allows RefreshTable to work normally.
Default is False.
This property is reset to False automatically after the calling procedure ends (important)
This property should be set to true just before you make an update (e.g. changing pivot item Visible property)
Below is some code written in C# as an example:
private void FilterByPivotItems(PivotField pf, List<string> pivotItemNames)
{
PivotItems pis = pf.ChildItems;
if (pf.Orientation != 0)
{
int oldAutoSortOrder = 0;
if (pf.AutoSortOrder != (int)Constants.xlManual)
{
oldAutoSortOrder = pf.AutoSortOrder;
pf.AutoSort((int)Constants.xlManual, pf.Name);
}
int pivotItemsCount = pf.PivotItems().Count;
for (int i = 1; i <= pivotItemsCount; i++)
{
PivotItem pi = pf.PivotItems(i);
var match = pivotItemNames.FirstOrDefault(stringToCheck => stringToCheck.Equals(pi.Value));
if (match == null)
{
TryFilterPivotItems(pi, false, true);
}
else
{
TryFilterPivotItems(pi, true, true);
}
}
if (oldAutoSortOrder != 0)
{
pf.AutoSort(oldAutoSortOrder, pf.Name);
}
PivotTable pt = pf.Parent as PivotTable;
if (pt != null)
{
pt.Update();
}
}
}
private void TryFilterPivotItems(PivotItem currentPI, bool filterValue, bool deferLayoutUpdate = false)
{
try
{
PivotField pf = currentPI.Parent;
PivotTable pt = pf.Parent as PivotTable;
if (currentPI.Visible != filterValue)
{
if (deferLayoutUpdate == true && pt != null)
{
pt.ManualUpdate = true;
currentPI.Visible = filterValue;
pt.ManualUpdate = false;
}
else
{
currentPI.Visible = filterValue;
}
}
}
catch (Exception ex)
{
}
}
private void TryFilterPivotItems(PivotField pf, string itemValue, bool filterValue, bool deferLayoutUpdate = false)
{
try
{
PivotItem currentPI = pf.PivotItems(itemValue);
TryFilterPivotItems(currentPI, filterValue, deferLayoutUpdate);
}
catch (Exception ex)
{
}
}
As a conclusion, ManualUpdate property change doesn't stay for long (in my tests, I could see that it gets reset to false as soon as possible, so that's why I recommended you to set it to true whenever you want to make a change for a pivot item)
For more info on what means an update in Excel, you can check the following:
Pivot Refresh vs. Update – is there a real difference?
References:
Title: Programming Excel with VBA and .NET
By: Jeff Webb, Steve Saunders
Print ISBN: 978-0-596-00766-9 | ISBN 10: 0-596-00766-3
Ebook ISBN: 978-0-596-15951-1 | ISBN 10: 0-596-15951-X