PDA

View Full Version : VBA to extract information from Pivot Table



bwwhite
04-30-2009, 05:30 AM
I have a Pivot Table that will either be created by hand or by code.

How can I use VBA to navigate around the table and extract information?

For example, suppose I have 3 columns in the Pivot Table: The first and second columns are just groups over which column 3 will provide a sum.
I know how to get the column 3 values as a collection of PivotItems, but I'd like to also be able to grab the corresponding values from columns 1 and 2 based on each item in column 3. I would prefer not to have to cycle through the collections every time.

Also, is it possible to obtain the cell address of a PivotItem or VisibleItem?

Ultimately, what I want to do is to allow a user to click a data cell (column 3) in the pivot table and then display a subset of ShowDetails data to the right of the Pivot Table. E.g., if I double click a data cell in the pivot table, I know I get a new sheet of columns that represents the ShowDetails data from my original data set. However, I only want to display a subset of all the columns of information and I'd like it displayed in the same worksheet as the pivot table, just offset to the right. Then in my application, when the user clicks a different data cell in the pivot table, the previous information is cleared out and the new information is displayed in its place.

Any help will be greatly appreciated!

Thank you!

Bruce

mdmackillop
04-30-2009, 02:36 PM
Hi Bruce,
Welcome to VBAX.
Can you post a workbook to demonstrate your layout? Add notes to make clear your requirements.

Use Manage Attachments on the Go Advanced reply page.

bwwhite
05-12-2009, 10:18 AM
I am attaching a spreadsheet that I think explains what I want. There are 2 tabs on the spreadsheet labeled "Data" and "Pivot".

The Data tab has the underlying data. The Pivot tab has a pivot table displaying a data summary. On that tab, I have an example of what I'm trying to do along with a textbox providing further explanation.

If I am still not clear enough, please let me know.

Thank you again!

Bruce

bwwhite
06-18-2009, 08:14 AM
I have attached an image showing what I want to do. Still looking for a solution.

Thanks!

Bruce

JWhite
06-19-2009, 03:52 PM
If you want them to see the "drill-down" detail, they could of course just double-click on the cell and they would get the underlying data on a separate sheet. I have pivottables with dozens of columns in the underlying data and my users are happy to double-click on an amount when they want to see the underlying detail. That way, they can manipulate the data in an Excel table in case they want to sort it or filter it.

But if you want them to see the underlying data on the same sheet as the pivottable, one way would be to simulate a drill-down. You could first trap any "select" event on that sheet and then check to see if they have selected a data-containing cell (check the activecell.pivotfield.orientation to see what kind of cell it is). If it is a data-containing cell, you could then do an actual drill-down (activecell.showdetail, I think) which creates a new sheet with the detail. You could then copy the table from that sheet on to the sheet with the pivottable and delete the sheet.

It seems awfully convoluted but I think it would work.

bwwhite
06-19-2009, 09:08 PM
Thanks for the input.

I am aware of the ability to double click on the table and get details. I don't want a separate sheet. Also, I don't want all the columns from the original database to appear, only certain ones.

Please look at the image I posted or the Excel file I uploaded. I'd like the details to appear above and to the right of the table. If I can get it do that via a mouse hover over the table cell, that would be wonderful, but if I have to click in the cell (creating and active cell) to do it, that would work, too.

I thought I might be able to do it via some kind of SQL query based on information from the pivot table. I thought, based on a results cell in the pivot, I could retrieve the correponding grouping data and execute a query to retrieve only the columns of interest from the original data.

I just don't know how to do this.

Thanks!

Bruce

bwwhite
06-25-2009, 07:39 AM
Bump.

Anyone? Thanks!

Bruce

JWhite
06-25-2009, 09:35 AM
I do quite a bit of work with pivottables and I don't know any way to just pull certain columns out of pivotcache and display them except in a pivottable, of course.

As I said in my previous post, you can simulate a double-click which puts all the columns of data in a separate sheet in an Excel table. You can then select any columns you want from the table and display them on the original sheet. And then you would delete the new sheet which was created when you simulated the double-click. Naturally you would turn off screen updating while all that was going on. I think it would actually run very quickly.

If anyone knows a way to pull certain columns out of a pivotcache and display them directly, I'd love to hear it.

bwwhite
07-06-2009, 07:00 AM
JWhite,

Thanks for the tips. I have taken your suggestion and have attached my solution. I use a command button to invoke the macro that does all the work. Rather than deleting unwanted columns, I plan to just group and hide the columns of secondary interest on the Pivot tab. That way they, too, are accessible if ever of interest.

Thanks again!

Bruce

JWhite
07-06-2009, 07:06 AM
Nice! And it's fast, too. I'm glad you brought up the problem because it's given me some new ideas for my application.

charliebrown
08-02-2011, 06:33 AM
Thanks a lot, ... not many samples of code available around to do that.

krysolov
01-23-2012, 01:14 PM
What happened to the attachments? I'd love to see some of this code, as I need to do something similar and there are barely any examples out there that deal with this. :-(

mdmackillop
01-23-2012, 02:10 PM
I'm afraid a lot of old attachments got lost when servers were changed. Please post your question in a new thread.

wilsonae
08-15-2012, 08:21 AM
What happened to the attachments? I'd love to see some of this code, as I need to do something similar and there are barely any examples out there that deal with this. :-( I was wondering exactly the same thing.