none
How to Programmatically Retrieve the Column Heading for a Cube Measure RRS feed

  • Question

  • Excel 2013.

    I am building a pivot table in VBA based on a data source.  In the data source, the column heading for one measure is "9.1.30J51."  When I try to set the column heading for this measure using the CubeField.Caption property.  Unfortunately, this returns "9130J51" having removed the periods in the original header.  If I add this measure to the pivot table using the wizard, it grabs the original header, as desired.  How can I do this in VBA so the periods are not removed?  My code block is below.


        For Each objcubefld In .PivotTables(1).CubeFields
          I = InStr(objcubefld.Name, "Sum of")
          If objcubefld.CubeFieldType = xlMeasure And I > 0 Then
             MeasureName = objcubefld.Name
             ColHead = objcubefld.Caption
             .PivotTables("PivotTable1").AddDataField .PivotTables( _
                 "PivotTable1").CubeFields(MeasureName), ColHead
          End If
        Next objcubefld
    

    Tuesday, April 12, 2016 7:43 PM

Answers

  • Hi Doug,

    Do you mean “9.1.30J51.” for ColHead changed to “9130J51” after you run this code? I made a test with the code below, but I failed to reproduce your issue with Excel 2013.

    Sub PivotTableTest()
    With ActiveSheet
    For Each objcubefld In .PivotTables(1).CubeFields
          I = InStr(objcubefld.Name, "Test Case Count")
          If objcubefld.CubeFieldType = xlMeasure And I > 0 Then
             MeasureName = objcubefld.Name
             ColHead = objcubefld.Caption
             .PivotTables(1).AddDataField .PivotTables(1).CubeFields(MeasureName), "9.1.30J51."
          End If
        Next objcubefld
    End With
    End Sub

    For a general suggestion, I suggest you output the ColHead to check the value.

    >> If I add this measure to the pivot table using the wizard, it grabs the original header, as desired.

    I suggest you try to record macro for this steps to check the recorded code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 14, 2016 5:34 AM

All replies

  • Hi Doug Pruiett,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, April 13, 2016 6:22 AM
  • Hi Doug,

    Do you mean “9.1.30J51.” for ColHead changed to “9130J51” after you run this code? I made a test with the code below, but I failed to reproduce your issue with Excel 2013.

    Sub PivotTableTest()
    With ActiveSheet
    For Each objcubefld In .PivotTables(1).CubeFields
          I = InStr(objcubefld.Name, "Test Case Count")
          If objcubefld.CubeFieldType = xlMeasure And I > 0 Then
             MeasureName = objcubefld.Name
             ColHead = objcubefld.Caption
             .PivotTables(1).AddDataField .PivotTables(1).CubeFields(MeasureName), "9.1.30J51."
          End If
        Next objcubefld
    End With
    End Sub

    For a general suggestion, I suggest you output the ColHead to check the value.

    >> If I add this measure to the pivot table using the wizard, it grabs the original header, as desired.

    I suggest you try to record macro for this steps to check the recorded code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 14, 2016 5:34 AM