Excel PivotTable filter issue when refreshing

1
votes

I have a ppivot table in Excel 2007 which has a Label Filter on one of the date fields for "Greater then or Equal to" a certain date. I have a macro that updates the source of the pivot table and refreshes it. After the refresh the table comes up empty and I have to go to the filter and just click OK (the filter value is still defined there) to see the data in the pivot. Why doesnt the filter get applied after the refresh?

Here is my code snippet:

Set c = ThisWorkbook.Connections.Item("Connection1")
c.OLEDBConnection.CommandText = "exec DB1.dbo.sp_GetnewData"        
c.Refres

For Each pivot In ThisWorkbook.Worksheets("Sheet1").PivotTables
    pivot.RefreshTable
Next
2
excelexcel-2007pivot-tablevba
Not the turnout I hoped for... I guess it boils down to this: How do I deal with filters after I refresh my pivot with VBA? – Lukasz

2 Answers

1
votes

I had the same problem and found a workaround - do not refresh entire workbook by clicking Refresh all, but refresh only data connection - this way your data will be up to date and your filtering won't be removed.

Hope this helps

Mojmir

1
votes

To keep the filters select the target table and open the Data tab. Click on Properties in the "Connections" section.

Check Preserve column sort/filter/layout