Field list for pivot table in VBA (2003 SP3)

Topic

New Reply

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%5B/url%5D

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?

Reply | Quote

Replies

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. . 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.

Reply | Quote

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

Reply | Quote

Sorry. It would be a stretch to say I was conversant with coding pivot tables! Everything I know I learned from the article I mention in my first post.

Reply | Quote