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!

 

[RoryA]

If you want the value too, use this instead:

Function PivotInfo(rngInput As Range) As String
   Dim pc As PivotCell
   Dim pf As PivotField, pi As PivotItem
   Dim strOut As String
   On Error Resume Next
   Set pc = rngInput.PivotCell
   On Error GoTo err_handle
   If pc Is Nothing Then
      PivotInfo = "Not a pivot cell"
   Else
      Select Case pc.PivotCellType
         Case xlPivotCellValue  'Any cell in the data area (except a blank row).
            If pc.RowItems.Count Then
               strOut = "Row items: " & vbLf
               For Each pi In pc.RowItems
                  strOut = strOut & pi.Parent.Name & ": " & pi.Value & vbLf
               Next pi
            End If
            If pc.ColumnItems.Count Then
               strOut = strOut & "Column items: "
               For Each pi In pc.ColumnItems
                  strOut = strOut & vbLf & pi.Parent.Name & ": " & pi.Value
               Next pi
            End If
            strOut = strOut & vbLf & pc.PivotField.Name & ": " & pc.Range.Value
         Case Else
            strOut = "Not a pivot data cell"
      End Select
   End If
   PivotInfo = strOut
Exit Function
 
err_handle:
   PivotInfo = "Unknown error"
End Function

You'll want to apply wrap text format to the cell to make it easier to read.

 

[peterbel]

That's brilliant, thanks very much.

I am only really interested in displaying the data to allow the user to confirm they have selected the right 'payment', and an OK message Box would do for this.
Your code would be actioned by a Button, then I want to parse strOut to assign the parts of it required for the Expenditure Table added line.
My next challenge!

If you are visiting Cornwall allow plenty of time for travelling. The M5 (Devon), A30 and A38 have all been horrendous recently, and they are the only Main roads we have!

 

[RoryA]

I know - my in-laws live down there so I've made that trip many, many times! I've spent more hours on the 303 than I care to recall...

 

[peterbel]

Ah yes, the A303, probably the worst of the lot!

 

[peterbel]

Is it possible to 'feed' this function with a User selected cell from the Pivot Table. I have tried something like chosen=ActiveCell.Address but it errors where as chosen=ActiveCell.Value doesn't !
I am trying to simplify the user actions for posting values to another Table (see objectives listed earlier) and hoped to make it, click to select, button press to action.
The problem is I don't know enough!

 

[RoryA]

You can just pass Activecell as the argument to the function above.

 

[peterbel]

Great, thanks