Hi,

I'm progamming a pivot table using VBA. I have no problem manipulating
column or row fields, but how can I replace a data field? Everything I try
just adds the data field to the existing data field resulting in a mess.

The PivotTable.addfields methods *replaces* whatever columns were previously
designated as RowFields, ColumnFields, and PageFields with the new
specifications, as in:

..AddFields RowFields:="Date", PageFields:="Location"
But no matter what I try, I can't replace the data field.... I've even tried
setting the Orientation property to xlHidden, as follows:

' Hide existing data fields
For each pf In .DataFields
Debug.Print "deleting pf", pf.Name
pf.Orientation = xlHidden
'pf.Delete ' I tried deleting too
Next

They key is that I need to do this via code. I can easily do it by using the
Excel GUI. I recorded the resulting code - Excel was hidding the field by
setting its Orientation property to xlHidden. Why doesn't that work for me?

Many thanks in advance,

Robert Stober

A quick paste of what I wrote a while ago...
maybe not be entirely geared to what you need, but
the PivotsLayout does contain what you're asking.


Sub PivotsCreate()
With ActiveWorkbook
On Error Resume Next
.Names("dnPivSource").Delete
With Worksheets("Pivots")
.PivotTables("Pivot1").TableRange2.Clear
.PivotTables("Pivot2").TableRange2.Clear
End With
On Error GoTo 0
.Names.Add "dnPivSource", _
"=OFFSET(ReadbyAdo!$A$1,0,0,COUNTA(ReadByAdo!$A:$A ),COUNTA
(ReadByAdo!$1:$1))"
With .PivotCaches.Add(xlDatabase, "dnPivSource")
.CreatePivotTable [Pivots!A3], "Pivot1"
.CreatePivotTable [Pivots!Z3], "Pivot2"
End With
End With
End Sub

Sub PivotsLayout()
Dim heads As Variant
Dim pt As PivotTable
Dim pf As PivotField

heads = [dnPivSource].Resize(1)
For Each pt In Worksheets("Pivots").PivotTables
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
.AddFields Array(heads(1, 4), heads(1, 5), heads(1, 1)), _
Array(heads(1, 2)), _
Array(heads(1, 3))
If pt.Name = "Pivot1" Then
.AddDataField .PivotFields(heads(1, 6)), "Top5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlTop, 5, "Top5"
.AutoSort xlDescending, "Top5"
End With
Else
.AddDataField .PivotFields(heads(1, 6)), "Bot5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlBottom, 5, "Bot5"
.AutoSort xlAscending, "Bot5"
End With
End If
End With
Next
End Sub

Sub PivotsResetSource()
Dim pt As PivotTable
Debug.Print "Changing Source, notice no update event"
ActiveWorkbook.Names.Add "dnPivSource", _
"=OFFSET(AltData4Pivot!$A$1,0,0,COUNTA(AltData4Piv ot!$A:$A),COUNTA
(AltData4Pivot!$1:$1))"
Debug.Print "Source Changed"
Debug.Print "Refresh cache, notice both tables are updated"
Worksheets("Pivots").PivotTables(1).PivotCache.Ref resh
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Robert Stober" wrote:

Hi,

I'm progamming a pivot table using VBA. I have no problem manipulating
column or row fields, but how can I replace a data field? Everything I
try just adds the data field to the existing data field resulting in a
mess.

The PivotTable.addfields methods *replaces* whatever columns were
previously designated as RowFields, ColumnFields, and PageFields with
the new specifications, as in:

.AddFields RowFields:="Date", PageFields:="Location"
But no matter what I try, I can't replace the data field.... I've even
tried setting the Orientation property to xlHidden, as follows:

' Hide existing data fields
For each pf In .DataFields
Debug.Print "deleting pf", pf.Name
pf.Orientation = xlHidden
'pf.Delete ' I tried deleting too
Next

They key is that I need to do this via code. I can easily do it by
using the Excel GUI. I recorded the resulting code - Excel was hidding
the field by setting its Orientation property to xlHidden. Why doesn't
that work for me?

Many thanks in advance,

Robert Stober

Thanks keepitcool,

Do mean this line?:

.AddDataField .PivotFields(heads(1, 6)), "Top5", xlSum

Is AddDataField support by Excel 2000, or just 2002? I don't see it in my
object browser - I'm using 2000.

Do you know how to do it in Excel 2000, or even an Excel '97 safe way...?

Thanks again,

Robert Stober


"keepitcool" wrote in message
...
A quick paste of what I wrote a while ago...
maybe not be entirely geared to what you need, but
the PivotsLayout does contain what you're asking.


Sub PivotsCreate()
With ActiveWorkbook
On Error Resume Next
.Names("dnPivSource").Delete
With Worksheets("Pivots")
.PivotTables("Pivot1").TableRange2.Clear
.PivotTables("Pivot2").TableRange2.Clear
End With
On Error GoTo 0
.Names.Add "dnPivSource", _
"=OFFSET(ReadbyAdo!$A$1,0,0,COUNTA(ReadByAdo!$A:$A ),COUNTA
(ReadByAdo!$1:$1))"
With .PivotCaches.Add(xlDatabase, "dnPivSource")
.CreatePivotTable [Pivots!A3], "Pivot1"
.CreatePivotTable [Pivots!Z3], "Pivot2"
End With
End With
End Sub

Sub PivotsLayout()
Dim heads As Variant
Dim pt As PivotTable
Dim pf As PivotField

heads = [dnPivSource].Resize(1)
For Each pt In Worksheets("Pivots").PivotTables
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
.AddFields Array(heads(1, 4), heads(1, 5), heads(1, 1)), _
Array(heads(1, 2)), _
Array(heads(1, 3))
If pt.Name = "Pivot1" Then
.AddDataField .PivotFields(heads(1, 6)), "Top5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlTop, 5, "Top5"
.AutoSort xlDescending, "Top5"
End With
Else
.AddDataField .PivotFields(heads(1, 6)), "Bot5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlBottom, 5, "Bot5"
.AutoSort xlAscending, "Bot5"
End With
End If
End With
Next
End Sub

Sub PivotsResetSource()
Dim pt As PivotTable
Debug.Print "Changing Source, notice no update event"
ActiveWorkbook.Names.Add "dnPivSource", _
"=OFFSET(AltData4Pivot!$A$1,0,0,COUNTA(AltData4Piv ot!$A:$A),COUNTA
(AltData4Pivot!$1:$1))"
Debug.Print "Source Changed"
Debug.Print "Refresh cache, notice both tables are updated"
Worksheets("Pivots").PivotTables(1).PivotCache.Ref resh
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Robert Stober" wrote:

Hi,

I'm progamming a pivot table using VBA. I have no problem manipulating
column or row fields, but how can I replace a data field? Everything I
try just adds the data field to the existing data field resulting in a
mess.

The PivotTable.addfields methods *replaces* whatever columns were
previously designated as RowFields, ColumnFields, and PageFields with
the new specifications, as in:

.AddFields RowFields:="Date", PageFields:="Location"
But no matter what I try, I can't replace the data field.... I've even
tried setting the Orientation property to xlHidden, as follows:

' Hide existing data fields
For each pf In .DataFields
Debug.Print "deleting pf", pf.Name
pf.Orientation = xlHidden
'pf.Delete ' I tried deleting too
Next

They key is that I need to do this via code. I can easily do it by
using the Excel GUI. I recorded the resulting code - Excel was hidding
the field by setting its Orientation property to xlHidden. Why doesn't
that work for me?

Many thanks in advance,

Robert Stober

sorry didn't check for downward compatibility.
dont have xl2000, just 97,xp and2003b2 :)

When unsure of syntax the macrorecorder will often prvide the necessary
clues.

adapted from recording in Excel97

.PivotFields(heads(1,6)).Orientation = xlDataField

but be carefull.. once brought into the data area the name of the field
appears to be different... however when you subsequently address is as
..DataFields(1) you should be ok

With ActiveSheet.PivotTables("Pivot1")
.DataFields(1).Function = xlSum
.DataFields(1).Name = "Hi There"
.RowFields(1).AutoSort xlDescending, .DataFields(1).Name
.RowFields(1).AutoShow xlAutomatic, xlTop, 5, .DataFields(1).Name
End With



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Robert Stober" wrote:

Thanks keepitcool,

Do mean this line?:

.AddDataField .PivotFields(heads(1, 6)), "Top5", xlSum

Is AddDataField support by Excel 2000, or just 2002? I don't see it in
my object browser - I'm using 2000.

Do you know how to do it in Excel 2000, or even an Excel '97 safe
way...?

Thanks again,

Robert Stober

I, too, have been struggling with the same problem in Excel 2000.

I've recorded a macro where I've dragged a field into the DataField
area and pulled it back out again to hide it, but when I try to play
the macro back, it fails with the message "Unable to set the
Orientation property of the PivotField class". Here's the code:

Sub Macro8()

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("EndBal")
.Orientation = xlDataField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of
EndBal").Orientation = xlHidden <====== FAILS HERE (also fails with
"EndBal" instead of "Count of EndBal")

End Sub

So far, the only workaround that seems to do the job is to delete the
cell with the fieldname in it: Range("A4").Delete

Of course, you have to figure out which cell has the field header in it
in order to delete it. Have you found any other way to do it?

Dave G



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Further tests indicate a possible corruption in the data causing the
problem. The field I couldn't remove from the datafield area started
working OK when I refreshed the data from the source.

Dave G



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

This snippet works in xl97 AND xlXP (sorry can't test xl2k)
it's as flexible as possible as it avoids "hardcoding"...

datafields are added last to avoid maximum size..

you'll need sheets "data" and "pivots"


ActiveWorkbook.Names.Add "dnPivSource", _
"=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(D ata!$1:$1))"

heads = [dnPivSource].Resize(1)

On Error Resume Next
Worksheets("Pivots").PivotTables("Pivot1").TableRa nge2.Clear
On Error GoTo 0

Set pt = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
"dnPivSource").CreatePivotTable([Pivots!A1], "Pivot1")
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
.AddFields _
RowFields:=Array(heads(1, 1), heads(1, 2)), _
ColumnFields:=Array(heads(1, 3)), _
PageFields:=Array(heads(1, 4))
.PivotFields(heads(1, 5)).Orientation = xlDataField
.DataFields(1).Function = xlSum
.DataFields(1).Name = "Top5"
.RowFields(1).AutoSort xlDescending, .DataFields(1).Name
.RowFields(1).AutoShow xlAutomatic, xlTop, 5,.DataFields(1).Name
.RowFields(1).Subtotals(1) = False
End With


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


DaveGulliksen wrote:

I, too, have been struggling with the same problem in Excel 2000.

I've recorded a macro where I've dragged a field into the DataField
area and pulled it back out again to hide it, but when I try to play
the macro back, it fails with the message "Unable to set the
Orientation property of the PivotField class". Here's the code:

Sub Macro8()

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("EndBal")
Orientation = xlDataField
Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of
EndBal").Orientation = xlHidden <====== FAILS HERE (also fails with
"EndBal" instead of "Count of EndBal")

End Sub

So far, the only workaround that seems to do the job is to delete the
cell with the fieldname in it: Range("A4").Delete

Of course, you have to figure out which cell has the field header in
it
in order to delete it. Have you found any other way to do it?

Dave G



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/