none
Group dates in Excel pivot table by month using VB.NET? RRS feed

  • Question

  • I want to group dates by month and then set months into the xlColumnsField orientation. But I can't get the commented out section to work. Do you know how this can be done?

    I think the problem might be related to how I pass the array into Periods. In VBA The correct expression was:

    Periods:=Array(False, False, False, False, True, False, False)

    But the array function was not found in VB.NET

    With TargetWks.PivotTables(Name).PivotFields("Project")
        .Orientation = Excel.XlPivotFieldOrientation.xlRowField
        .Position = 1
    End With
    With TargetWks.PivotTables(Name).PivotFields("Invoiced")
        .Orientation = Excel.XlPivotFieldOrientation.xlPageField
        .Position = 1
    End With
    With TargetWks.PivotTables(Name).PivotFields("Hours")
        .Orientation = Excel.XlPivotFieldOrientation.xlDataField
        .Position = 1
    End With
    TargetWks.PivotTables(Name).AddDataField(TargetWks.PivotTables(Name).PivotFields("Hours"), "Sum of Hours", Excel.XlConsolidationFunction.xlSum)
    
    'TargetWks.PivotTables(Name).PivotFields("Date").Group(Start:=True, End:=True, Periods:={False, False, False, False, True, False, False})
    'With TargetWks.PivotTables(Name).PivotFields("Date")
    '.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
    '.Position = 1
    'End With


    • Edited by JP3O Monday, March 16, 2015 10:28 AM
    Monday, March 16, 2015 10:27 AM

Answers

  • Hi

    If you want to group your pivot table, you can using the following code

    Dim myrange As Range

    Dim PT As PivotTable

    Set PT = ActiveSheet.PivotTables(1)

    Set myrange = PT.PivotFields("Date").DataRange.Cells(1)

    myrange.Select

    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _

           False, True, False, False)

    Instead of

    'TargetWks.PivotTables(Name).PivotFields("Date").Group(Start:=True, End:=True, Periods:={False, False, False, False, True, False, False})

    When the Range object represents a single cell in a PivotTable field’s data range, the Group method performs numeric or date-based grouping in that field.  The following link provide the way how to use “Group”.

    https://msdn.microsoft.com/EN-US/library/office/ff839808.aspx?f=255&MSPPError=-2147217396

    Hope this will help you

    Best Regards

    Lan


    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.

    • Marked as answer by L.HlModerator Tuesday, March 24, 2015 10:50 AM
    Tuesday, March 17, 2015 9:32 AM
    Moderator