locked
Pivot Tables Group Fields RRS feed

  • Question

  • Hi

    When I group dates field in a pivot table. I get new "Month" and "Year" fields.

    When I refer to the new fields using VBA with the same language they were created, there is no problem.

    but when a person that has an Excel installed in another language runs the code,

    he can encounter a 1004 error "Unable to get the PivotFields property of the pivot table class"

    since excel does not recognize the field "Years" (In his computer the field will get the equivalent of the word years in his language)

    How do I solve the problem without having to have codes for each language installed?

    Thanks

    Saturday, January 27, 2018 10:19 PM

Answers

  • Hello MaYaLi,

    If the field name is not "Year" in the other Excel, you could not use name as index to get the pivot table field object. 

    I would suggest you use number index to get the field from fields index.

    For instance

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    For i = 1 To pt.PivotFields.Count
    Debug.Print pt.PivotFields(i).Name
    Next i

    Best Regards,

    Terry


    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.

    • Proposed as answer by Terry Xu - MSFT Tuesday, January 30, 2018 1:42 AM
    • Marked as answer by MaYaLi Tuesday, January 30, 2018 12:56 PM
    Monday, January 29, 2018 5:56 AM

All replies

  • Hello MaYaLi,

    If the field name is not "Year" in the other Excel, you could not use name as index to get the pivot table field object. 

    I would suggest you use number index to get the field from fields index.

    For instance

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    For i = 1 To pt.PivotFields.Count
    Debug.Print pt.PivotFields(i).Name
    Next i

    Best Regards,

    Terry


    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.

    • Proposed as answer by Terry Xu - MSFT Tuesday, January 30, 2018 1:42 AM
    • Marked as answer by MaYaLi Tuesday, January 30, 2018 12:56 PM
    Monday, January 29, 2018 5:56 AM
  • Thank you

    Monday, January 29, 2018 11:42 AM
  • Hello MaYaLi,

    I note that you said thanks. Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    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.

    Tuesday, January 30, 2018 1:43 AM