excel - Pivot Table Field has no items -
i working on series of pivot tables on excel, particular filter automated in following way: user inputs particular value in 1 cell, , filters across workbook refresh accordingly. have been reading posts in stack exchange explain how it. however, have come across issue have been unable solve.
my data comes external microsoft access database. have created connexion between database , excel file; data imported excel file displayed table tblexcel
. pivot tables linked tblexcel
.
to refresh filters, want use following line:
item.visible = (item.caption = cd)
where item
pivotitems
object , cd
value inputted user. line wasn't working, wrote following subroutine check something:
sub test() check = 0 each field in application.activeworkbook.worksheets("sheet1").pivottables("pivottable").pivotfields("[tblexcel].[field1].[field1]").pivotitems check = check + 1 next msgbox check end sub
it turns out msgbox
returns invariably 0, fields, empty.
does know why happening? due data model structure? how circumvent it?
try following code, debug purposes have inputbox
, write down field1 value want pivot table filter to.
option explicit public sub filter_pivottable_items() dim pvttbl pivottable dim pvtfld pivotfield dim cd string ' setting pivot table pivottable object set pvttbl = activeworkbook.worksheets("sheet1").pivottables("pivottable") application.screenupdating = false ' set pivot table show selected project's data set pvtfld = pvttbl.pivotfields("field1") ' select manually value field 1 >> debug purpose cd = inputbox("select item") call selectpivotitem(pvtfld, cd) end sub public sub selectpivotitem(field pivotfield, cd string) dim item pivotitem on error goto cd_error each item in field.pivotitems item.visible = (item.caption = cd) next cd_error: if err.number = 1004 msgbox "item " & cd & " not found !" exit sub end if end sub
Comments
Post a Comment