locked
can write but cant read a power pivot table field DrilledDown property using VBA RRS feed

  • Question

  • Hello

    I am writing some code so that users can save a current view of their pivot table and want to read whether each field is expanded (DrilledDown) or not.  I am having trouble reading the property which always returns 'False'

    The following code updates the current pivot table, in this case expanding the field
    ActiveSheet.PivotTables("PivotTable3").PivotFields("[Table1].[Fld1].[Fld1]").DrilledDown = True, so I can set the property.
    But if I try to read the current DrilledDown value, using similar code the value returned is always "False"

    To test, If I type the following vba sequence in the immediate window

    ActiveSheet.PivotTables("PivotTable3").PivotFields("[Table1].[Fld1].[Fld1]").DrilledDown = True
    ?ActiveSheet.PivotTables("PivotTable3").PivotFields("[Table1].[Fld1].[Fld1]").DrilledDown
    False

    The field displays all records but reading the value always returns "False" even when it was just set to "True"

    Am I missing something?  Thanks in anticipation.

    Thursday, January 25, 2018 7:58 PM

All replies

  • Hi PQUK,

    Thanks for your question.

    According to your description, your problem is more related to VBA. To solve your question more efficiently, please post your question in Excel for Developers forum: https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 26, 2018 1:58 AM