Refresh Pivot Table message

L

Lucky

I update (using Access) periodically several Excel
workbooks containing bunch of Pivot Tables (PT, current
count is 68, but the number changes frequently). During
the update I get the following message:

"The Refresh Data operation changed the Pivot Table
report." This stops the update cold till the OK button
is clicked.

I previously used the following code to disable at the
start and enable at the end the refresh operation:

Set xlSht = xlWbk.Worksheets("Sheet1")
XlSht.PivotTables
("PT1").PivotCache.RefreshOnFileOpen = False
Set xlSht = Nothing

But with so many PT being created and changed, it is
impossible to get them all.
So I have replaced the individual enable/disable code to
the following code:

With xlWbk.Application
.DisplayAlerts = False
.AlertBeforeOverwriting = False
.ScreenUpdating = False
.AskToUpdateLinks = False
End With

But it occasionally allows the Refresh message through.
Any ideas how the have 100% protection against the
message popping up in my update, yet refreshing the PT
when users see them?

Any ideas are greatly appreciated.

Thank you.

Lucky
 
Top