none
How to Reference Elements of a Pivot Table from Code Behind RRS feed

  • Question

  • Excel 2013.

    I want to reference various elements of a pivot table from code behind an Excel sheet.  How can I programmatically reference a pivot table, do operations like expanding and contract rows, reference values in rows, etc.?  Would appreciate any help.  If there is a nice tutorial somewhere, that would be great.  Thank you.

    For Example, I have a pivot table named PhasePivot.  The rows (fields) are Phase, Office, Folder Path, and Report, in that order.  I want to programmatically access the values in the "Folder Path" rows for a given Phase.

    Friday, August 21, 2015 1:30 PM

Answers

  • Thanks ryguy.

    I am getting old and forget stuff.  While I was waiting for someone to post back, the Lord reminded me that I can always use macro recording in Excel to see the code to accomplish tasks.  I turned on recording and began moving about and changing things in the pivot table.  Sure enough, it gave me the code I need for expanding, collapsing, referencing pivot tables, pivot fields, and pivot items.

    The lesson here . . .

    IF YOU NEED CODE EXAMPLES USE "RECORD MACRO" to get code for things that are hard to find by searching the net or looking at the cryptic and marginally useful documentation at msdn.microsoft.com/EN-US/library/office.

    Friday, August 21, 2015 3:17 PM

All replies

  • Please take a look at this site.

    http://www.contextures.com/tiptech.html

    Go down to 'Pivot Tables' and check out those samples.  You'll find lots of useful things there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, August 21, 2015 2:26 PM
  • Thanks ryguy.

    I am getting old and forget stuff.  While I was waiting for someone to post back, the Lord reminded me that I can always use macro recording in Excel to see the code to accomplish tasks.  I turned on recording and began moving about and changing things in the pivot table.  Sure enough, it gave me the code I need for expanding, collapsing, referencing pivot tables, pivot fields, and pivot items.

    The lesson here . . .

    IF YOU NEED CODE EXAMPLES USE "RECORD MACRO" to get code for things that are hard to find by searching the net or looking at the cryptic and marginally useful documentation at msdn.microsoft.com/EN-US/library/office.

    Friday, August 21, 2015 3:17 PM
  • You are right.  Gotta love the Macro Recorder.

    God bless!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, August 21, 2015 3:20 PM