How to replace PivotTable data fields...?

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


0
rstober (6)
10/5/2003 1:33:38 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
753 Views

Similar Articles

[PageSpeed] 47

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(AltData4Pivot!$A:$A),COUNTA
(AltData4Pivot!$1:$1))"
Debug.Print "Source Changed"
Debug.Print "Refresh cache, notice both tables are updated"
Worksheets("Pivots").PivotTables(1).PivotCache.Refresh
End Sub



keepITcool

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


"Robert Stober" <rstober@charter.net> 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
> 
> 
> 

0
xrrcvgpbby (46)
10/5/2003 1:49:32 AM
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" <xrrcvgpbby@puryyb.ay> wrote in message
news:Xns940B26B5188C6keepitcoolnl@207.46.248.16...
> 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(AltData4Pivot!$A:$A),COUNTA
> (AltData4Pivot!$1:$1))"
> Debug.Print "Source Changed"
> Debug.Print "Refresh cache, notice both tables are updated"
> Worksheets("Pivots").PivotTables(1).PivotCache.Refresh
> End Sub
>
>
>
> keepITcool
>
> < email   : keepitcool chello nl (with @ and .) >
> < homepage: http://members.chello.nl/keepitcool >
>
>
> "Robert Stober" <rstober@charter.net> 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
> >
> >
> >
>


0
rstober (6)
10/5/2003 2:01:45 AM
Reply:

Similar Artilces:

Synchronize HQ data from SO
I curretly have 2 stores that usually receive inventory update from HQ. However during the last few weeks I had to data entry from each RMS clients (SO). Now I want to update inventory data at HQ using worksheet. But it seems not possible, although sales data show correctly. Is there any way ? -- Many Thanks In an HQ environment all new items must be entered from HQ and sent to stores. Did you create new items at store level ? You may want to create those items in HQ and send to store. Mihir Shah Diviasoft, Inc. www.diviasoft.com "cosamo" <cosamo@discussions.microsoft.c...

Entering Data to make a list?
Is there a way that when I enter something into A1 &B1 it will fill down on a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will copy Apples and $12 , then if I type Pears & $5 it will copy to the next blank cell and so on -- Thanks in advance for your help....Bob Vance .. .. .. .. Bob I'm assuming you have this list on another sheet. This little macro will do the following: When you enter something in B1 of the first sheet, it will copy A1:B1 to the bottom of the list in Columns A & B of the second sheet. It will then clear (erase) the conte...

Pls recommend a replacement for Microsoft Query
Hello: could anyone recommend a third-party solution to query sql server database from Excel, a replacement for the stinking Microsoft Query? thanks, Vadim Rapp Vadim I don't have a suggestion, but is there anything specific you can't do with the sql/msquery combo? I've not used sql, but regularly use Client access with AS400 and Access. Mostly flawlessly. I tend however to write the query in the server tool, rather than try and query in MSQuery -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Vadim Rapp"...

how to avoid closing dialog if data validation fails
I would like to validate data entered in a dialog when the OK button is clicked. If there are problems with the data I would like to avoid closing the dialog and keep it open. Can anyone suggest how this is done? Thanks for any suggestions. Ian If you dont want to close your dialog in OnOk, do not call the base class OnOK. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "ian" <ib252@n0spam.com> wrote in message news:F1nnb.26812$He4.1099447@wagner.videotron.net... | I would like to validate data entered in a dialog when the OK button is | clicked. If there are problem...

Push data to safe senders list to Outlook possible?
Is it possible to push a safe domain list to everyone's Outlook Junk Email safe senders list from Exchange (or any other method)? We are on Exchange 2003, all clients are Outlook 2003. Thanks. On Mon, 13 Feb 2006 13:10:29 -0800, "David" <David@discussions.microsoft.com> wrote: >Is it possible to push a safe domain list to everyone's Outlook Junk Email >safe senders list from Exchange (or any other method)? We are on Exchange >2003, all clients are Outlook 2003. >Thanks. Outlook11.adm in a GPO will allow you to set the default directory clients use...

Data Lables, series and Scatterplots
Is there any way to get excel scatterplots to recognize a third column (or row) as a series for a scatterplot? FOr example I have data as: Sequence Long Lat 1 49.12 28.05 2 49.17 28.34 3 49.5 28.48 4 49.17 28.36 I would like to plot Long(itude) as X axis and Lat(itude) as Y on scatterplot (which I can do). But I would also like to identify the sequence number fopr each point. I thought I could do that by getting the Sequence numbers to represent a series, but can't sseem to get that in a scatterplot. Is there any other chart...

Sorting column data
How do I sort the data in the columns, I want to make the appear in alphabetical order? Alan I recommend Excel help. Search for: Sort a range. -- Steve "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appear in > alphabetical order? > > Alan > > "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appe...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

Resolve Case, Resolution Type Field (Need more options)
I need to add more options to the Resolution Type Field (There is only the PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list but the form Resolve Case and its fields do not appear anywhere, Please let me know how can i do this if somebody knows it.... -- cB - Thanks in advance I'm having the same issue! "cB" wrote: > I need to add more options to the Resolution Type Field (There is only the > PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list > but the form Resolve Case and its fields do not appear...

Refreshing query data via vba button
I have a wookbook which has 5 worksheets that contains sql query's that are bulling data from an access 2003 db, instead of me right clicking on each worksheet to refresh the data, is there any vba code that i can import that will enable the user to refresh the worksheet via a single button, been racking my brain, could someone help me please.............!!!!!!!!!!!!!!!!! -- RzaXL ThisWorkbook.RefreshAll MIke F "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message news:5A2F6E4B-5A45-4AA2-8F4E-CEA3AFE0C1F5@microsoft.com... >I have a wookbook which...

Excel 2007: Return rownumber when text data on 3 columns match exa
Using: Excel 2007 I want to document Windows Group Policy settings side by side on a single sheet. To accomplish this task I want to return the row number of the current sheet where the data of the row that has an exact match. The data I want to lookup is in three colums. Example: Column B | Column C | Column D ------------------------------------------------------------------------------------------- Policy | Control Panel | Load a specific visual style Policy | Desktop | Do not add shares Policy | Desktop | Hide Network Location...

Blank To and From Fields
We are on Exchange 2003 and my folks are receiving e-mail messages where both TO and FROM fields are blank. Is there a way to block messages when both TO and FROM are Blank? This is what the header looks like.... X-Comment: Sending client does not conform to RFC822 minimum requirements X-Comment: Date has been added by Maillennium Received: from X.X.X.X (unknown[X.X.X.X](misconfigured sender)) Thanks! -- NC Beach Bum And they are SPAM, right? Exchange 2003 allows you to filter messages that have a Blank sender. If you want to do this, you must enable it in 2 places. First, under Glo...

Lookup data in the list
Hi All, I have a list as shown bellow, I want to pick up the date correcponding to the value on cell A1 and the value in B2 or B3. So it is some thing like this =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example} ColumnA ColumnB ID Name 1 12082A IN-tuition 2 Ms. Gayle 3 Gayle Theresa 4 Sofiane 5 12082B tuition 6 Ms. Gayle 7 12298A Language Institute 8 Senouci Bereksi 9 AOUL 10 Bereksi Kindly Help ASAP, As I am Stuck in my assignment. Hi, Manish. The better way, of course, is to have the ID in col A, the company name in B, the individual name in C a...

Big trouble matching with data on excel!
Hi, I'm required to present some info at my work, using two excel files. This is the task: File 1, has ID numbers in column A, and column B is blank. File 2, has ID numbers in column C and column D has the date the person joined the company File 1 es a select grop of people (about 2500), whereas File 2 is the general database (about 30thou) They're asking me to match the data, in order to end up with the coresponding date they joined the company in column B on File 1 (obtained from column D on file 2), how to do it? taking in account that File 1 is outdated and ...

How to let field sales people use our data base (without ASP/Scripting) ??
We have a data base we want our field sales people people to use daily. We have all our queries and forms and reports done in Access. We do not want to have to re-do it all in ASP or some other Scripting Language. We think we need something like Citrix and Terminal Server... but they are expensive... right? What are the alternatives available to very small businesses? Thanks for any help. Mel "Mel" <MyEmailList@gmail.com> wrote in message news:1180126800.016508.53210@q69g2000hsb.googlegroups.com... > We have a data base we want our field sales people people to use ...

clearing data
hi guys, i'm new to MBS great plains... i needed help in clearing all the data in the financial, sales and purchasing module (includes all transactions, vendors, customers and etc....) in great plains without deleting the set-up (includes chart of accounts and all) for the 3 modules. i would really appreciate it if you guys can give me the procudures on how to do it... -- iandale You could use File >> Maintenance >> Clear Data. Clear data from things that say "transaction" and "history". Keep away from things that say "setup" or "o...

Exporting Extender Data
Does anyone know which table holds the extender data? I tried searching customersource for some sort of extender table structure, but I couldn't find anything. I want to be able to export this data using crystal reports, but I just need to know how to connect to it. Try Techknowledge #899573 "ConnollyandCo" wrote: > Does anyone know which table holds the extender data? I tried searching > customersource for some sort of extender table structure, but I couldn't find > anything. I want to be able to export this data using crystal reports, but I > just n...

How to Extract Data from a CSV file???
I need help on how to write a VB command that will allow me to extract data from a specified column in a CSV file. I have written a command in VB to extract all data from the CSV file and paste it on an excel worksheet. This method is working fine but because the file is too big, it takes a long time for me to extract everything. I only need the data from one column so if anyone out there can help me with this, I would be extremely grateful... Cheers ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly fro...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Use Find and Replace in Excel
I have the following in cells after using a formula. #INVALID_DATA. The reason is there is no data in the cell. If i use the Find and Replace to remove the unwanted data in the cells I first use the find all command. I change the Formulas to Values. I can see the unwanted data at the bottom. I then use the Replace command. At that time I try to change the Formulas to Values. There is no Values under the replace. There is a Values in the Find section. What do I do to replace #INVALID_DATA in the cells with nothing, like using ""? I do not know what #INVALID_DATA is. Th...

Link variable data from one worksheet to another worksheet?
I am trying to link variable data from one worksheet (my source worksheet) to several other worksheets. For example, my source worksheet has two variables or columns: 'Team' and 'Bye Week'. The first entry under the 'team' column reads 'NE'. Let's say for this example that the first entry under the 'Bye Week' column is the number '10'. Given this, I want every entry under the 'Team' column in other worksheets that read 'NE', to read the number '10' under the 'Bye Week' column. Let's also say tha...

How do I resize a data label box in a pie chart to stop text wrapp
How do I resize a data label box in a pie chart to stop text wrapping? Excel decides how large to make these pseudo text boxes, based on the chart size, the amount of text, and the font properties. Aside from changing these, you have no control over the label sizes. You could replace the labels with textboxes, but these don't stay attached to a data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Eton Careers" <Eton Careers@discussions.microsoft.com> wrote in message news:B18C20DE-FD79...

Macro to shift all source data in a chart by one row or column?
Hello all, I have a spreadsheet which contains at least twenty years of data and twenty or thirty charts. Each year we update all of the charts to move the forecast period to the current year and the next five years. Because there is data well beyond the five year period, a dynamic range for the chart data would not work. Can anyone suggest if it is possible to write a macro which I can run on each chart to shift the source data for each series in the chart by one column or row to the right? Any suggestions would be gratefully received! Thanks in advance, Shan try this defined ...

Am confused about a simple data layout- need help asap!
This is really silly, because I am good at charts, but for some reason, I cannot formulate in my head the proper layout of this chart. Here is what I have/need: THESE ARE MY DATA HEADERS: A B C D E F PERSON JOB 1 JOB 2 JOB 3 TOTAL ARBITRARY 1-3 NUMBER THE DATA LOOKS LIKE THIS: A B C D E F NAME1 3 2 8 ...

Label designer fields?
Hello all, Is any one aware of a way to put one of the dimensions of a matrix item on a label? I found a table called dbo_ItemClassComponent, which contains a section called "Detail1" that has all the values I need. I've tried using the fields for Details that were in the designer with no luck. Any help would be greatly appreciated. ...