locked
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

Answers

  • 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.
    Worksheets(1).PivotTables(1).PivotCache.Refresh
    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