Grouping Excel Pivot Table by month and year

Get help with using AutoHotkey and its commands and hotkeys
aifritz
Posts: 84
Joined: 29 Jul 2018, 11:30
Location: Germany

Grouping Excel Pivot Table by month and year

13 Jan 2019, 12:21

Hi together,

I'm trying to group data in an excel Pivot Table by month and year.

This ahk-code is working and groups only by month:

Code: Select all

...
xl.Range("A8").Group(1)
...
but how does it work, grouping by month and year!?

This is the code from Excel macro recorder which works, when I run the vba code to do the grouping by month and year:

Code: Select all

    
    Range("A8").Select
    Selection.Group Start:=39112, End:=43477, Periods:=Array(False, False, False, False, True, False, True)
Running in Autohotkey this code,

Code: Select all

...
MyArray := ComObjArray(VT_VARIANT:=12, 7)
MyArray[0] := False
MyArray[1] := False
MyArray[2] := False
MyArray[3] := False
MyArray[4] := True
MyArray[5] := False
MyArray[6] := True
xl.Range("A8").Group(Start:=39112, End:=43477, MyArray)
...
gives me the following error: (... strange, but in this case the Pivot Table is also grouped by month again!?)

Error: 0x800A03EC -
Source: Microsoft Excel
Description: Die Group-Eigenschaft des Range-Objektes kann nicht zugeordnet werden.
Translation from DeepL: The Group property of the Range object cannot be assigned.

The description of the Group-Method doesn't really help me: :roll:
https://docs.microsoft.com/en-us/office ... ange.group

Has someone an idea what's wrong here?

br aifritz
aifritz
Posts: 84
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 04:40

With the help of this site
http://www.globaliconnect.com/excel/ind ... Itemid=475
it throws no error anymore, but grouping by years is still ignored :roll:

Any ideas are welcome :angel:

Code: Select all

MyArray := ComObjArray(VT_VARIANT:=12, 7) ;creates a safearray with 7 elements
MyArray[0] := true  ;element 1 indicates the Period of Seconds
MyArray[1] := false ;element 2 is Minutes
MyArray[2] := false ;element 3 is Hours
MyArray[3] := false ;element 4 is Days
MyArray[4] := true  ;element 5 is Months
MyArray[5] := false ;element 6 is Quarters
MyArray[6] := true  ;element 7 is Years

rngGroup := xl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Angel.am").DataRange
rngGroup.cells(1).Group(MyArray) ;rngGroup.Cells(1) indicates the first cell in the range of rngGroup, cause RangeObject in the Group Method should only be a single cell otherwise the method will fail.

User avatar
Xeo786
Posts: 193
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 05:21

I also had issues while translating vba into ahk com but try this,

Code: Select all

;~ expression. Group( _Start_ , _End_ , _By_ , _Periods_ )
rngGroup.cells(1).Group(,,,MyArray)
all 4 fields in group(f1,f2,f3,f4) are option so left them as they are so they act as default, and i think you know what you were doing wrong, you placed MyArray at 3rd feild "_by_" these commas really matters :)

Code: Select all

;~ expression. Group( _Start_ , _End_ , _By_ , _Periods_ )
xl.Range("A8").Group(Start:=39112, End:=43477, MyArray)
"When there is no gravity, there is absolute vacuum" -Game changer theory
aifritz
Posts: 84
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 06:00

Hi Xeo786,

thank you for your input :)
;~ expression. Group( _Start_ , _End_ , _By_ , _Periods_ )
rngGroup.cells(1).Group(,,,MyArray)
Using the code above, leads for me again to the error:
Error: 0x800A03EC -
Source: Microsoft Excel
Description: The Group property of the Range object cannot be assigned.
:(
Also using this variant, gives me the same error

Code: Select all

_ := ComObjMissing()
rngGroup.cells(1).Group(_,_,_,MyArray)
User avatar
Xeo786
Posts: 193
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 07:13

try this `rngGroup.cells(1).Group.Periods := Array(False, False, False, False, True, False, True)` if you get error again share your excel template having vb code
"When there is no gravity, there is absolute vacuum" -Game changer theory
aifritz
Posts: 84
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 10:13

Hi Xeo786,
rngGroup.cells(1).Group.Periods := Array(False, False, False, False, True, False, True)`
This brings no error, but grouping doesn't work (still only for months) :?

Here the test-scenario in Excel:
You just need two columns with the titles "Date" column A and "VArt" Column B in Sheet1

Date | VArt
01.03.2018 | 1
02.05.2018 | 1
03.01.2017 | 2
02.02.2017 | 2
04.05.2018 | 3
05.06.2018 | 3

Then run this VBA macro...

Working fine for me with Excel 2013. May be you had to adopt the xlPivotTableVersion to your version:
https://docs.microsoft.com/en-us/dotnet ... =excel-pia

Code: Select all

Sub Makro5()
' Makro5 Makro
    NumRows = ActiveSheet.UsedRange.Rows.Count
    Sheets.Add.Name = "Evaluation" 'inserts a new sheet for Pivot Table
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & NumRows & "C2", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Evaluation!R3C1", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion15
    Sheets("Evaluation").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("VArt")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("VArt"), "Amount of VArt", xlCount
    Range("A5").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, True)
End Sub
br aifritz
awel20
Posts: 136
Joined: 19 Mar 2018, 14:09

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 11:05

Range.Group says that Periods is "An array of Boolean values"
So maybe try this:

Code: Select all

VT_BOOL:=0xB
MyArray := ComObjArray(VT_BOOL, 7)
MyArray[0] := False
MyArray[1] := False
MyArray[2] := False
MyArray[3] := False
MyArray[4] := -1
MyArray[5] := False
MyArray[6] := -1
xl.Range("A8").Group(True, True,, MyArray)
aifritz
Posts: 84
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Grouping Excel Pivot Table by month and year

14 Jan 2019, 14:13

Many thanks to all!!

Only with your help could I get it to work :D :D :D

It's important here to use VT_Bool instead of VT_VARIANT and the commas must be set in the Group method!
(true and -1 work both)

Code: Select all

MyArray := ComObjArray(0xB, 7)  ;0xB => VT_Bool
MyArray[0] := false  ;element 1 indicates the Period of Seconds
MyArray[1] := false ;element 2 is Minutes
MyArray[2] := false ;element 3 is Hours
MyArray[3] := false ;element 4 is Days
MyArray[4] := true ;element 5 is Months
MyArray[5] := false ;element 6 is Quarters
MyArray[6] := true  ;element 7 is Years

rngGroup := PT.PivotFields("Angel.am").DataRange
rngGroup.cells(1).Group(,,,MyArray) 
br aifritz

Return to “Ask For Help”

Who is online

Users browsing this forum: fabioposadaz, Flipeador, fryvha, PinkySuavo, Snuffy and 95 guests