none
Pivot Table Filters Not Updating RRS feed

  • Question

  • Hello:

    I have an application in Microsoft Access 2013 that links to Excel 3013 to open a workbook and update a worksheet within that workbook with new data from the Access database.  There is another worksheet in that workbook the contains a pivot table that uses the data worksheet I updated from Access.

    Everything works.... except...

    The pivot table correctly summarizes the new data which Access populated on the data worksheet.  However, there is a filter set up in the Pivot table that I can't seem to update from VBA.

    Here's a snippet of the code:

    ' ***********************************************************************
    ' Refresh Pivot Table
    ' ***********************************************************************
    strPivotName = "MovementsPivot"
    
    Set rngStartPoint = xlSelectedMovementData.Range("A1")
    Set rngDataRange = xlSelectedMovementData.Range(rngStartPoint, rngStartPoint.SpecialCells(xlLastCell))
    strNewRange = xlSelectedMovementData.Name & "!" & rngDataRange.Address(ReferenceStyle:=xlR1C1)
    
    'Change Pivot Table Data Source Range Address
    xlPivotTableWorksheet.PivotTables(strPivotName).ChangePivotCache _
        xlWB.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=strNewRange)
    
    'Ensure Pivot Table is Refreshed
      xlPivotTableWorksheet.PivotTables(strPivotName).RefreshTable
      xlPivotTableWorksheet.PivotTables("MovementsPivot").PivotCache.Refresh
      xlPivotTableWorksheet.PivotTables("MovementsPivot").Update
    xlPivotTableWorksheet.PrintOut

    After I update the workbook, I save it.  When I open it, everything looks good except when I try to use the filter, I get this message:

    The PivotTable report was saved without the underlying data.Use the Refresh Data command to update the report.

    Sure enough, if I click the "Refresh Data" button on the ribbon, the filters are displayed correctly.  My workaround is to create an xlsm file with the WorkbookOpen event, which does the refresh.  But I would like to do it from Microsoft Access.

    This is what I had to put into the Excel workbook, basically the same command I put into Access.

    Private Sub Workbook_Open()
    Sheets("Data Pivot").PivotTables("MovementsPivot").PivotCache.Refresh
    End Sub

    Any ideas?


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Monday, January 29, 2018 7:28 AM
    Monday, January 29, 2018 7:23 AM

Answers

  • Moderator:  You can close this issue.  Thanks.

    After a long an tedious set of attempts, I was finally able to properly update both the pivot table from refreshed source data, but also correctly have the system automatically update the filter.  Here's the code.

    Public Sub ChangePivotTableAndPivotFilter()
    Dim xlWorkbook            As Workbook
    Dim xlShSales             As Worksheet
    Dim xlShPivot             As Worksheet
    Dim xlPivotTable          As PivotTable
    Dim xlPivotCache          As PivotCache
    Dim rngStartPoint         As Range
    Dim rngNewRange           As Range
    Dim strNewRangeString     As String
    Dim lngLastRow            As Long
    Dim lngLastColumn         As Long
    Dim rngLastCell           As Range
    
    ' ********************************************************
    ' Update the Pivot Table
    ' ********************************************************
    Set xlWorkbook = ThisWorkbook
    Set xlShSales = xlWorkbook.Sheets("Sales")
    Set xlShPivot = xlWorkbook.Sheets("SalesPivot")
    Set xlPivotTable = xlShPivot.PivotTables("TestThePivot")
    Set xlPivotCache = xlPivotTable.PivotCache
    
    ' ********************************************************
    ' Look up the updated last row and column after
    ' Access updated the data. Convert To a Range
    ' ********************************************************
    lngLastRow = xlShSales.Cells(xlShSales.Rows.Count, "A").End(xlUp).Row
    lngLastColumn = xlShSales.Cells(2, xlShSales.Columns.Count).End(xlToLeft).Column
    Set rngLastCell = xlShSales.Cells(lngLastRow, lngLastColumn)
    
    Set rngStartPoint = xlShSales.Range("A1")
    Set rngNewRange = xlShSales.Range(rngStartPoint, rngLastCell)
    
    ' ********************************************************
    ' Set up the new range string used by ChangePivotCache
    ' ********************************************************
    strNewRangeString = ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & "Sales!" & rngNewRange.Address(ReferenceStyle:=xlR1C1)
    
    ' ********************************************************
    ' Ask Excel to change the pivot table's data source
    ' ********************************************************
    xlShPivot.PivotTables("TestThePivot").ChangePivotCache xlWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=(strNewRangeString), Version:=6)
    
    ' ********************************************************
    ' Tidy up with refreshes of the pivot table
    ' ********************************************************
      xlShPivot.PivotTables("TestThePivot").RefreshTable
      xlShPivot.PivotTables("TestThePivot").PivotCache.Refresh
      xlShPivot.PivotTables("TestThePivot").Update
      
    End Sub
    

    The "ChangePivotCache" was the trick.  But I had to provide the new range of data with a string.  I also had to find the last row and column to create the last cell's range.  The built-in SpecialCells(xlLastCell) doesn't work because it isn't reset when the source data columns are enlarged or shrunk.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, January 29, 2018 10:52 PM
  • One more important note:

    Unless the following statement to save data is added just before the save, you will encounter an error every time you try to use the filter that says data wasn't saved.  This little statement only took me two days to track down.

    ' **************************************************************
    ' Save it to the network drive
    ' **************************************************************
    xlPivotTableWorksheet.PivotTables("MovementsPivot").SaveData = True
    xlWB.SaveAs FileName:=strSavedFileName, FileFormat:=xlOpenXMLWorkbook


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by RichLocus Tuesday, January 30, 2018 4:48 PM
    Tuesday, January 30, 2018 5:04 AM

All replies

  • Moderator:  You can close this issue.  Thanks.

    After a long an tedious set of attempts, I was finally able to properly update both the pivot table from refreshed source data, but also correctly have the system automatically update the filter.  Here's the code.

    Public Sub ChangePivotTableAndPivotFilter()
    Dim xlWorkbook            As Workbook
    Dim xlShSales             As Worksheet
    Dim xlShPivot             As Worksheet
    Dim xlPivotTable          As PivotTable
    Dim xlPivotCache          As PivotCache
    Dim rngStartPoint         As Range
    Dim rngNewRange           As Range
    Dim strNewRangeString     As String
    Dim lngLastRow            As Long
    Dim lngLastColumn         As Long
    Dim rngLastCell           As Range
    
    ' ********************************************************
    ' Update the Pivot Table
    ' ********************************************************
    Set xlWorkbook = ThisWorkbook
    Set xlShSales = xlWorkbook.Sheets("Sales")
    Set xlShPivot = xlWorkbook.Sheets("SalesPivot")
    Set xlPivotTable = xlShPivot.PivotTables("TestThePivot")
    Set xlPivotCache = xlPivotTable.PivotCache
    
    ' ********************************************************
    ' Look up the updated last row and column after
    ' Access updated the data. Convert To a Range
    ' ********************************************************
    lngLastRow = xlShSales.Cells(xlShSales.Rows.Count, "A").End(xlUp).Row
    lngLastColumn = xlShSales.Cells(2, xlShSales.Columns.Count).End(xlToLeft).Column
    Set rngLastCell = xlShSales.Cells(lngLastRow, lngLastColumn)
    
    Set rngStartPoint = xlShSales.Range("A1")
    Set rngNewRange = xlShSales.Range(rngStartPoint, rngLastCell)
    
    ' ********************************************************
    ' Set up the new range string used by ChangePivotCache
    ' ********************************************************
    strNewRangeString = ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & "Sales!" & rngNewRange.Address(ReferenceStyle:=xlR1C1)
    
    ' ********************************************************
    ' Ask Excel to change the pivot table's data source
    ' ********************************************************
    xlShPivot.PivotTables("TestThePivot").ChangePivotCache xlWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=(strNewRangeString), Version:=6)
    
    ' ********************************************************
    ' Tidy up with refreshes of the pivot table
    ' ********************************************************
      xlShPivot.PivotTables("TestThePivot").RefreshTable
      xlShPivot.PivotTables("TestThePivot").PivotCache.Refresh
      xlShPivot.PivotTables("TestThePivot").Update
      
    End Sub
    

    The "ChangePivotCache" was the trick.  But I had to provide the new range of data with a string.  I also had to find the last row and column to create the last cell's range.  The built-in SpecialCells(xlLastCell) doesn't work because it isn't reset when the source data columns are enlarged or shrunk.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, January 29, 2018 10:52 PM
  • One more important note:

    Unless the following statement to save data is added just before the save, you will encounter an error every time you try to use the filter that says data wasn't saved.  This little statement only took me two days to track down.

    ' **************************************************************
    ' Save it to the network drive
    ' **************************************************************
    xlPivotTableWorksheet.PivotTables("MovementsPivot").SaveData = True
    xlWB.SaveAs FileName:=strSavedFileName, FileFormat:=xlOpenXMLWorkbook


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by RichLocus Tuesday, January 30, 2018 4:48 PM
    Tuesday, January 30, 2018 5:04 AM
  • Hi RichLocus,

    You had mentioned that,"Moderator:  You can close this issue.  Thanks."

    It's good to know that you got the solution by yourself.

    Currently, Only OP has right to mark the answer.

    To close this thread, You need to mark your own post as an answer.

    Then only, We are able to close this thread from our side.

    This thread will remain open until you mark the answer.

    I suggest you to mark your second post as an answer.

    It will help us to close this thread.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 30, 2018 6:47 AM
    Moderator