i have parameter file below set filters , subroutine read parameters , set filters in pivottable. filters first 2 columns (lines 2 [environment] , 3 [ent_period_end_dt]) work expected, values set. third column (row 4) not. puzzling part code runs statement pf.clearallfilters aborts 1004 item not found in olap cube on line statement pf.currentpagename = filterval. recorded manual change filter , code fails same error. manually setting filter on sheet works fine. possible there in powerpivot model preventing filter being changed in vba. suggestions?
parameters
worksheet,filter,value,type occupancy (monthly),[environment].[environment].[environment],[environment].[environment].&[qagdw],string occupancy (monthly),[dim_calendar_d].[ent_period_end_dt].[ent_period_end_dt],[dim_calendar_d].[ent_period_end_dt].&[2016-03-31t00:00:00],date occupancy (monthly),[supp_portfolio_d].[supplemental_portfolio_name].[supplemental_portfolio_name],[supp_portfolio_d].[supplemental_portfolio_name].&[operations],string and sub-routine
sub setpivotfilters() dim filtercol string dim filterval string myworksheetpath = thisworkbook.path myparmfilename = split(thisworkbook.name, ".")(0) & "_parms.csv" open thisworkbook.path & "\" & myparmfilename input #1 row_number = 0 'application.enableevents = false activesheet.pivottables("pivottable3").pivotcache.refresh until eof(1) line input #1, linefromfile lineitems = split(linefromfile, ",") filtercol = lineitems(1) filterval = lineitems(2) 'skip heading line in parm file if row_number > 0 dim pf pivotfield each pvt in activesheet.pivottables myname = pvt.name 'pvt.manualupdate = true set pf = pvt.pivotfields(filtercol) pf.clearallfilters pf.currentpagename = filterval next end if
