PivotTable.columnfields(index) vs Pivotcell.columnitem(index)
 

I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other words,
columnitems use the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the pivottable),
regardless of its actual location it is placed relative to the other
column fields.
Apparently, this causes errors with GetPivotData because there is no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1), pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.

PivotTable.columnfields(index) vs Pivotcell.columnitem(index)
 

Hi Jason

ColumnFields and RowFields return the values in the order of the columns
in the source data, not however they are arranged on the PT.

In Cells G1 and H1 of my sheet I set up Sales and Marketing. In F2 and
F3 I set up CA and FL

The following code looped through and filled the cells for me with data
from the PT.

Sub test()
Dim PT As PivotTable, i As Long, j As Long
Set PT = Worksheets("Sheet2").PivotTables("Pivottable3")
For i = 2 To 3
For j = 7 To 8

Cells(i, j) = PT.GetPivotData(PT.ColumnFields(1), _
PT.RowFields(1), "US", _
PT.RowFields(2), Cells(i, 6), _ 'State
PT.ColumnFields(1), Cells(1, j), _ 'Department
PT.ColumnFields(2), "Dell")
Next j
Next i
End Sub

Clearly the Values for US and Dell could have been set to be read from
other variables if required.

--
Regards

Roger Govier


"Crypto" wrote in message
oups.com...
I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other words,
columnitems use the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the pivottable),
regardless of its actual location it is placed relative to the other
column fields.
Apparently, this causes errors with GetPivotData because there is no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1), pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.

PivotTable.columnfields(index) vs Pivotcell.columnitem(index)
 

Thanks so very much for taking the time to reply to my question.
In the line where it says Cells(i, j) =
PT.GetPivotData(PT.ColumnFields(1), _
Did you mean "pcell.datafield"? It issues an error with the
PT.Columnfields(1)

Also I wonder how the worksheet function GetPivotData automatically
pick up the matching columnfield and columnitem when you click in the
pivot table. If GetPivotData uses the same objects and properties that
are available in VBA, it means there is a way to know the columnfield
name (eg. Company) corresponding to a certain columnitem. (eg. Dell)

On Jun 19, 4:06 am, "Roger Govier"
wrote:
Hi Jason

ColumnFields and RowFields return the values in the order of the columns
in the source data, not however they are arranged on the PT.

In Cells G1 and H1 of my sheet I set up Sales and Marketing. In F2 and
F3 I set up CA and FL

The following code looped through and filled the cells for me with data
from the PT.

Sub test()
Dim PT As PivotTable, i As Long, j As Long
Set PT = Worksheets("Sheet2").PivotTables("Pivottable3")
For i = 2 To 3
For j = 7 To 8

Cells(i, j) = PT.GetPivotData(PT.ColumnFields(1), _
PT.RowFields(1), "US", _
PT.RowFields(2), Cells(i, 6), _ 'State
PT.ColumnFields(1), Cells(1, j), _ 'Department
PT.ColumnFields(2), "Dell")
Next j
Next i
End Sub

Clearly the Values for US and Dell could have been set to be read from
other variables if required.

--
Regards

Roger Govier

"Crypto" wrote in message

oups.com...



I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other words,
columnitemsuse the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the pivottable),
regardless of its actual location it is placed relative to the other
column fields.
Apparently, this causes errors with GetPivotData because there is no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1), pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.- Hide quoted text -

- Show quoted text -

PivotTable.columnfields(index) vs Pivotcell.columnitem(index)
 

Hi jason

In my PT source table, I had Country, State, Department, Company in that
order.
In the PT I have Country, then State as row Items
Department then Company as Column items
My Data field is Count of Department.

PT.ColumnFields(1) returns Department for me, which is correct.
The code runs fine for me, using XL2003

If you were using Count of State as your data field, then you would need
to amend the code to

Cells(i, j) = PT.GetPivotData(PT.RowFields(2), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

Because Data is being obtained from a Row field, and it is the second
row field.

Similarly, if there were a 5th column titled Value, with numbers like
20, 30, 40 etc. and the Sum of Values was what was added to the data
area, then it would be

Cells(i, j) = PT.GetPivotData(PT.DataFields(1), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")
--
Regards

Roger Govier


"Crypto" wrote in message
ups.com...
Thanks so very much for taking the time to reply to my question.
In the line where it says Cells(i, j) =
PT.GetPivotData(PT.ColumnFields(1), _
Did you mean "pcell.datafield"? It issues an error with the
PT.Columnfields(1)

Also I wonder how the worksheet function GetPivotData automatically
pick up the matching columnfield and columnitem when you click in the
pivot table. If GetPivotData uses the same objects and properties that
are available in VBA, it means there is a way to know the columnfield
name (eg. Company) corresponding to a certain columnitem. (eg. Dell)

On Jun 19, 4:06 am, "Roger Govier"
wrote:
Hi Jason

ColumnFields and RowFields return the values in the order of the
columns
in the source data, not however they are arranged on the PT.

In Cells G1 and H1 of my sheet I set up Sales and Marketing. In F2
and
F3 I set up CA and FL

The following code looped through and filled the cells for me with
data
from the PT.

Sub test()
Dim PT As PivotTable, i As Long, j As Long
Set PT = Worksheets("Sheet2").PivotTables("Pivottable3")
For i = 2 To 3
For j = 7 To 8

Cells(i, j) = PT.GetPivotData(PT.ColumnFields(1), _
PT.RowFields(1), "US", _
PT.RowFields(2), Cells(i, 6), _ 'State
PT.ColumnFields(1), Cells(1, j), _
'Department
PT.ColumnFields(2), "Dell")
Next j
Next i
End Sub

Clearly the Values for US and Dell could have been set to be read
from
other variables if required.

--
Regards

Roger Govier

"Crypto" wrote in message

oups.com...



I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was
puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other
words,
columnitemsuse the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the
pivottable),
regardless of its actual location it is placed relative to the
other
column fields.
Apparently, this causes errors with GetPivotData because there is
no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1),
pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.- Hide quoted text -

- Show quoted text -

PivotTable.columnfields(index) vs Pivotcell.columnitem(index)
 

On Jun 19, 12:57 pm, "Roger Govier"
wrote:
Hi jason

In my PT source table, I had Country, State, Department, Company in that
order.
In the PT I have Country, then State as row Items
Department then Company as Column items
My Data field is Count of Department.

PT.ColumnFields(1) returns Department for me, which is correct.
The code runs fine for me, using XL2003

If you were using Count of State as your data field, then you would need
to amend the code to

Cells(i, j) = PT.GetPivotData(PT.RowFields(2), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

Because Data is being obtained from a Row field, and it is the second
row field.

Similarly, if there were a 5th column titled Value, with numbers like
20, 30, 40 etc. and the Sum of Values was what was added to the data
area, then it would be

Cells(i, j) = PT.GetPivotData(PT.DataFields(1), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")
--
Regards

Roger Govier

"Crypto" wrote in message

ups.com...

Thanks so very much for taking the time to reply to my question.
In the line where it says Cells(i, j) =
PT.GetPivotData(PT.ColumnFields(1), _
Did you mean "pcell.datafield"? It issues an error with the
PT.Columnfields(1)

Also I wonder how the worksheet function GetPivotData automatically
pick up the matching columnfield and columnitem when you click in the
pivot table. If GetPivotData uses the same objects and properties that
are available in VBA, it means there is a way to know the columnfield
name (eg. Company) corresponding to a certain columnitem. (eg. Dell)

On Jun 19, 4:06 am, "Roger Govier"
wrote:
Hi Jason

ColumnFields and RowFields return the values in the order of the
columns
in the source data, not however they are arranged on the PT.

In Cells G1 and H1 of my sheet I set up Sales and Marketing. In F2
and
F3 I set up CA and FL

The following code looped through and filled the cells for me with
data
from the PT.

Sub test()
Dim PT As PivotTable, i As Long, j As Long
Set PT = Worksheets("Sheet2").PivotTables("Pivottable3")
For i = 2 To 3
For j = 7 To 8

Cells(i, j) = PT.GetPivotData(PT.ColumnFields(1), _
PT.RowFields(1), "US", _
PT.RowFields(2), Cells(i, 6), _ 'State
PT.ColumnFields(1), Cells(1, j), _
'Department
PT.ColumnFields(2), "Dell")
Next j
Next i
End Sub

Clearly the Values for US and Dell could have been set to be read
from
other variables if required.

--
Regards

Roger Govier

"Crypto" wrote in message

groups.com...

I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was
puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other
words,
columnitemsuse the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the
pivottable),
regardless of its actual location it is placed relative to the
other
column fields.
Apparently, this causes errors with GetPivotData because there is
no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1),
pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.- Hide quoted text -

- Show quoted text -

Thanks again for your kind help!