View Single Post
  #1 (permalink)  
Old October 4th, 2003, 09:05 PM
rstober rstober is offline
Authorized User
 
Join Date: Sep 2003
Location: Escalon, CA, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to replace pivot table data fields using VBA?

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


Reply With Quote