Pivot Table Refresh

Bill G · Sep 14, 2004

When extracting data from an external source, the following message
appears when the spreadsheet opens:

"Microsoft Excel cannot refresh a Pivot Table until refresh of the
external data range on which the Pivot Table is based is complete."

Hitting the "OK" gets past the warning and the spreadsheet functions
normally. Is there any way to suppress the warning so the user does not
need to hit the "OK"?

Thanks for your help.

Guest · Sep 14, 2004

try Application.DisplayAlerts = False

William Gazda · Sep 14, 2004

Thanks for the suggestion but the message still appears on Open. Maybe I'm
not associating the Application.DisplayAlerts at the right place. I
associated it with the workbook Open. Is that the right association?

Thanks.

William Gazda · Sep 15, 2004

I replied to this yesterday don't no longer see my reply - apologies if this
is duplicated.

Thanks for the suggestion below. I tried it but the same message appears.
I associated it with the Workbook Open. Was that the correct association?

Thanks

Bill

William Gazda · Sep 16, 2004

Thanks for the suggestion. I tried it and had the same message presented.
I put the Application.DisplayAlerts=False on Workbook Open. If there is
another association I should be using......

Thanks

Bill

Toby Erkson · Sep 21, 2004

I use this for my report automations:

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Sheets("SheetNameGoesHere").Select 'When refreshing the workbook a
worksheet, NOT A CHART, needs to be active (dunno why?)
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True
End If
End Sub

Make sure you are NOT on a Chart sheet when you execute the RefreshAll. When
this is executed ALL data sources will update (refresh).

Bill G · Oct 3, 2004