Excel Slicer Manipulation with VBA -

-

i learning how create , manipulate excel slicers vba. have been following examples paul te braack on "business intelligence website: https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/

when try cycle through items of slicer cache , print values of "caption", "value" , "name" in immediate window, returns error of "run-time error '1004': application-defined or object-defined error line: " code: "set sl = sc.slicercachelevels(1)"

sub btest() dim sc slicercache dim sl slicercachelevel dim si sliceritem  application.enableevents = false application.calculation = xlmanual set sc = activeworkbook.slicercaches(5) set sl = sc.slicercachelevels(1) debug.print “——————————————————————————”  each si in sl.sliceritems debug.print “caption; – > ” & si.caption debug.print “value; – > ” + cstr(si.value) debug.print “unique; name; – > ” + si.name debug.print “——————————————————————————”  next application.enableevents = true application.calculation = xlcalculationautomatic  end sub

i modified code, modified code returns results "false," "false," , "sheet5false" returned. pivot table , slicer in sheet5 way. (which think should work based on slicer cache , item verifications mentioned below.)

when review slicer cached in locals window, shows has 30 items, , can expand items , display values of caption, value, , name.

when type "debug.print sc.sliceritems.count" in immediate window returns answer of 30. when type "debug.print sc.sliceritems(2).name in immediate window returns answer of 30.

the modified code is:

sub ctest() dim sc slicercache dim sl slicercachelevel dim si sliceritem  application.enableevents = false application.calculation = xlmanual set sc = activeworkbook.slicercaches(5) set si = sc.sliceritems(1) debug.print “——————————————————————————”  each si in sc.sliceritems debug.print “caption; – > ” & si.caption debug.print “value; – > ” + cstr(si.value) debug.print “unique; name; – > ” + si.name debug.print “——————————————————————————”  next application.enableevents = true application.calculation = xlcalculationautomatic  end sub

any guidance on doing wrong in original code example or modified code example be

sliceritemlevels applicable olap data feeds. see reference.

it seems website copied code used ascii characters 147 (“) & 148 (”) open , close quotes. vba expects ascii character 34 (") both. following should work fine.

sub ctest() dim sc slicercache dim sl slicercachelevel dim si sliceritem  application.enableevents = false application.calculation = xlmanual set sc = activeworkbook.slicercaches(5) set si = sc.sliceritems(1) debug.print "——————————————————————————"  each si in sc.sliceritems debug.print "caption; – > " & si.caption debug.print "value; – > " + cstr(si.value) debug.print "unique; name; – > " + si.name debug.print "——————————————————————————"  next application.enableevents = true application.calculation = xlcalculationautomatic  end sub

Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more