ExcelBanter - Pivot Table Refresh

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Pivot Table Refresh (https://www.excelbanter.com/excel-programming/309845-pivot-table-refresh.html)

Bill G[_3_]

Pivot Table Refresh

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.

No Name

Pivot Table Refresh

try Application.DisplayAlerts = False

-----Original Message-----
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.
.

William Gazda

Pivot Table Refresh

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.
wrote in message
...
try Application.DisplayAlerts = False

-----Original Message-----
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.
.

William Gazda

Pivot Table Refresh

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
wrote in message
...
try Application.DisplayAlerts = False

-----Original Message-----
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.
.

William Gazda

Pivot Table Refresh

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
wrote in message
...
try Application.DisplayAlerts = False

-----Original Message-----
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.
.

Toby Erkson[_3_]

Pivot Table Refresh

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).
--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

"Bill G" wrote in message
news:Keu1d.189663$mD.106356@attbi_s02...
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.

Bill G[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks. Just removed
the End If statement :). Thanks again.

Bill

Toby Erkson wrote:
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[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks. Just removed
the End If statement :). Thanks again.

Bill

Toby Erkson wrote:

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[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks. Just removed
the End If statement :). Thanks again.

Bill

Toby Erkson wrote:

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[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks. Just removed
the End If statement :). Thanks again.

Bill

Toby Erkson wrote:

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[_3_]

Pivot Table Refresh

Bill G[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks, i.e after I
removed the End If :). Thanks again.

Bill

Toby Erkson wrote:
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[_3_]

Pivot Table Refresh

Toby,

I tried your suggestion and it worked perfectly - thanks, i.e after I
removed the End If :). Thanks again.

Bill

Toby Erkson wrote:

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).

All times are GMT +1. The time now is 03:01 AM.