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!

 

[spattah]

Does someone maybe know if this ain't possible at all?

 

[Fazza]

Hi,

There are simpler approaches, I think. The pivot table is basically a summation of store sales from some data. Maybe from Access you can link to the data in Excel and do the summation in Access. Or populate the Access table via ADO with some SQL to do the summation. So, omit the pivot table. SQL like below. HTH, Fazza

SELECT product_id, store_id, Sum(store_sales) AS [store_sales]
FROM SourceData
GROUP BY product_id, store_id

 

[spattah]

Fazza, thank you for your answer.

But I'm not totally there. The thing is that the SQL statement you have made, assumes that the Tables which are put in the PivotTables are already know. This ain't the case.

See my previous code:

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

Here I identify the column and rowheaders. Now, for example, I want the value 11,4 to increase with 10%. The query I know have for that is:

strSQL = "UPDATE sales_fact_1997 SET sales_fact_1997.store_sales = sales_fact_1997.store_sales  *((110)/100)" & _
            "WHERE (((sales_fact_1997.product_id)= " & Row & ") AND ((sales_fact_1997.store_id)= " & Column & "));"

I'm able to send this query to my access database. But, the problem is now that I want to make this query more variable, by asking excel what the tables are. Eventually the code should look like this:

strSQL = "UPDATE theTable SET theTable.dataField = TheTable.dataField  *((110)/100)" & _
            "WHERE ((theTable.rowField)= " & Row & ") AND ((theTable.columnField)= " & Column & "));"

So, this query should do for all different pivottables I put in excel.

summarized, the VBA program should give my all properties of the value 11,4 in my example-table. This way I'm able to generate a query which works for all tables.

Hopefully I don't make the story to difficult

gr!

 

[Fazza]

Hi, spattah.

The story is a little difficult, though maybe that is because I'm not trying hard enough to understand it. It would really help if you state what you are trying to achieve.

What are the starting inputs - it seems like some source data in an Excel file - and what you are wanting to do. UPDATE some records in an Access database?

FWIW, from what I understand, this doesn't require a pivot table, and should be simple enough to do. Then again, maybe I don't understand what you want so I might be wrong about that.

I don't understand the comment "The thing is that the SQL statement you have made, assumes that the Tables which are put in the PivotTables are already know." Does this mean you don't know the source data for the pivot table?

regards, Fazza

 

[spattah]

THe thing is that the query must be dynamically appropriate for all databases. So when the user defines a pivotreport from a list of tables, my vba program must interact with this pivotreport in order to make the dynamic query. So if you look at my example table again, the cell the user selects in the datafield(11,4) must know out of which tables it is conducted. I need to get the following variables from excel in order to make the dynamic query:

columnheader
rowheader
rowtable
columntable

the top 2 variables I already figured out, but the last 2 variables, in the case of the example time_id and product_id, are not possible to conduct from the pivotreport for me..

Does this make it any clearer?

 

[Fazza]

Thanks. It isn't clear to me at all. So, I can not help you. Regards, Fazza

 

[RoryA]

Does this help:

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: " & vbLf
               For Each pi In pc.ColumnItems
                  strOut = strOut & vbLf & pi.Parent.Name & ": " & pi.Value
               Next pi
            End If
            strOut = strOut & pc.PivotField.Name
         Case Else
            strOut = "Not a pivot data cell"
      End Select
   End If
   PivotInfo = strOut
Exit Function
 
err_handle:
   PivotInfo = "Unknown error"
End Function

 

[Fazza]

Thanks, Rory.

I don't understand why a pivot table is used. Still wondering. Regards, Fazza

 

[RoryA]

It's too late at night for me to wonder why, so I just thought I'd throw that in!