Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field list for pivot table in VBA (2003 SP3)

    I have programmed a pivot table form for a client so that she can always look at her data from the same starting point but get a range of statistics. I have this working successfully based on Rick Dobson's article on msdn found here http://msdn.microsoft.com/en-us/library/aa662945.aspx

    What the article does not explain is how to make fields unused in the initial form available in the field list. Currently, the only ones I have available in my pivot table are the ones that I explicitly populate it with even though there are others available in my source query. So the question is: Is there a way when programming a pivot table to populate the field list with the fields from the underlying query that weren't used in the pivot table?

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field list for pivot table in VBA (2003 SP3)

    On some further research and fiddling with this, I realized that the difference between using an existing form for a pivot table and creating one in code is that when you follow the directions in the msdn article, you are creating a form with a recordsource but not actually putting anything on the form itself. I created a workaround by creating a form with all the fields on it that I want (everything in the underyling query), making its default view as a pivot table and then running the code to set up the various axes in the OnOpen event of the form. No matter what state the pivot table was left in during the last use, all of the fields get put where I want them to start which was the aim of the excercise: everytime the pivot table is opened, it has the same starting point. <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> . Even more importantly for me, by having the fields on the form, they also all appear on the field list which is what I needed.

    I'm still having some trouble with setting totals - if they already exist, the code breaks on naming the totals field. I will play with that code and see what I come up with or not try to set totals in code at all.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field list for pivot table in VBA (2003 SP3)

    Hi Peter, are you conversant with coding pivot charts as well?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field list for pivot table in VBA (2003 SP3)

    Sorry. It would be a stretch to say I was conversant with coding pivot tables! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Everything I know I learned from the article I mention in my first post.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •