Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot tables not refreshing after data source update
I'm trying to two refresh pivot tables on a worksheet that are bound
to the same data. I find that it takes two successive calls in order to get this to work. However, a "Refresh" button placed on the page that calls basically the same code works perfectly when clicked only one time. Below is the code to change the datasource query and refresh the pivot tables when the user changes one of the inputs on the page: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Trapper 'Reset command text and requery If (Target.Address = Me.Range("StartDate").Address Or Target.Address = Me.Range("EndDate").Address) Then With ActiveWorkbook.Connections("srv1658 JumboRolls").OLEDBConnection .CommandText = "My SELECT statement" .Refresh End With For Each t In Worksheets("Analysis").PivotTables t.PivotCache.Refresh Next End If Exit Sub Trapper: 'MsgBox Err.Description Exit Sub End Sub Here is the code called on a button click that works just fine Sub RefreshPivotTables() For Each t In Worksheets("Analysis").PivotTables t.PivotCache.Refresh Next End Sub Does anyone have any idea why the first block of code would not properly refresh the Pivot tables? It seems like a caching issue and perhaps I don't understand the cache well enough. Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot tables not refreshing after data source update
The resolution to this was to add
..BackgroundQuery = False to the OLEDBConnection just before the call to Refresh. Thank you to Bill Manville and Jan Karel Pieterse in the Compuserve Office forum for the suggestion. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
Programmatically refreshing a data source for a pivot table (olap) | Excel Programming | |||
Loosing formatting when refreshing data in Pivot tables Excel2003 | Charts and Charting in Excel | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
Pivot Tables - Refreshing with data??..... | Excel Programming |