Pivottable problem

[spattah]

Dear all,

I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:

This is a example table:

<table x:str="" style="border-collapse: collapse; width: 290pt;" width="387" border="0" cellpadding="0" cellspacing="0"><col style="width: 98pt;" width="131"> <col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 98pt;" width="131" height="17">Sum of store_sales</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">time_id</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">product_id</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">store_id</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">367</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">368</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">369</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="" align="right" height="17">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">6</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">7</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="11.4" align="right">11,4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">11</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">13</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">14</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
(the format is not real clear, but I will explain)

This is a part of a PivotTable where:
Sum of Store_sales is located in the datafield(one record; 11,4)
product_id and store_id are Row-Items
Time_id is a column item.

Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.

Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1)
Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1)
Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2)

But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.

Is someone able to give me a hint? Is there a method for this in VBA?

Thank you in advance!

 

[Fazza]

Thanks

 

[spattah]

Fazza, thank you for trying to understand me. I know my explanation was a little difficult.

But with some study into the code of rorya I managed to make it work! That code was a eye opener for me in terms of getting the pivotfield items. Thanks a lot both!

gr! Spattah!

 

[spattah]

one additional question: how do I get the value of the pagefield in the pivottable?

 

[RoryA]

You need the PageField's CurrentPage property.

 

[spattah]

Thank you. That does the job!

 

[peterbel]

RoryA, sorry for resurrecting this old post but this is excellent and useful code, exactly what I have been searching for! Thanks for posting
When I run it against my Pivot table everything returned is as expected except for the last strOut line:
"strOut = strOut & vbLf & pi.Parent.Name & ": " & pi.Value "
This returns the Value Field Name for pi.Value and not the contents of the cell? For example £77.85 in this PT is returned as 'Amount'

https://photos.app.goo.gl/mCDzMLmra11rzEA49

Can you suggest a remedy?

Thanks

 

[RoryA]

On which one of those lines?

Any chance you can post a sample file for me, rather than a photo?

 

[peterbel]

Thanks for responding Rory.
I have made a cutdown version of the file, as some of the info is for our holiday cottage accounts and so confidential, but cannot see how to post it !!
The objective for this particular sheet was to click on one of the PT cells to retrieve the information of Car, Year, Quarter and Amount so a line could be added to an Expenditure Table (not in the cutdown) as paid, the Expense Table on this sheet lines updated as Yes under Paid, and finally the Expenses PT refreshed so only the unpaid show.
Just need to find out how the send you the file now !!

 

[RoryA]

You'd need to put it on a file share like OneDrive or Dropbox, then post a link here. (or wait a couple of weeks until I'm down in Cornwall... )

 

[peterbel]

Hope this works!
https://www.dropbox.com/s/8ernu611m32a8rs/Test PT Info.xlsm?dl=0