VBA pivotfields.currentpage works in Excel, but not when invoked from VBScript RRS feed

  • Question

  • I have a refresh pivot table function that I successfully invoke from a VBScript. The process opens a csv file, copies data to a worksheet inside an excel file that I use as a template. The VBScript reads every csv in a directory, opens each file, copies to the template that has multiple pivot tables. The refresh pivot table function works beautifully. I am now extending that function to set a page filter on several of the pivot tables based on a value inside the spreadsheet. The VBA works perfectly as long as I execute it with the file open. When I invoke the refresh pivot table function from my VBScript, the PivotFields("filtername").ClearAllFilters works, but the PivotFields("filtername").CurrentPage = "somevalue" does not work.  The value of the filtername is set to (All) after the process runs.  I did add an Application.wait, but that does not do the trick. What would make this work?  I have about 26 files that all use this same template, and the ability to invoke this refresh function in batch saves hours of repetitive work.  Ideas?
    Saturday, March 19, 2016 9:49 PM


  • Hi, lojoy13

    According to your descrition, I suggest that you could try to refresh the PivotTable cache that causes the specified chart to be redrawn immediately.
    For more information, click here to refer about PivotCache.Refresh Method (Excel)

    • Marked as answer by David_JunFeng Wednesday, March 30, 2016 9:43 AM
    Tuesday, March 22, 2016 9:31 AM