How Do You Group Pivot Table Fields?

PowerShell Help
1

I am working on a PowerShell script to create pivot tables in Excel, for the first time. I have a field that has dates in it and I want to group on that field in the pivot table on the Days and the Months, but I cannot find how to do that through PowerShell.

#### Pivot Table
$xlPivotTableVersion15 = 5 # Excel 2013
$xlPivotTableVersion12 = 3 # Excel 2007
$xlPivotTableVersion10 = 1
$xlSum                 = -4157
$xlAverage             = -4106
$xlCount               = -4112
$xlRight               = -4152
$xlDescending          = 2
$xlDatabase            = 1  # this just means local sheet data
$xlHidden              = 0
$xlRowField            = 1
$xlColumnField         = 2
$xlPageField           = 3
$xlDataField           = 4    
$xlDirection           = [Microsoft.Office.Interop.Excel.XLDirection]

$range1 = $ExcelWorksheet.Range("C1")
$range1 = $ExcelWorksheet.Range($range1,$range1.End($xlDirection::xlDown))
$range2 = $ExcelWorksheet.Range("M1")
$range2 = $ExcelWorksheet.Range($range2,$range2.End($xlDirection::xlDown))
$selection = $ExcelWorksheet.Range($range1, $range2)

$PivotTable = $ExcelWorkbook.PivotCaches().Create($xlDatabase, $selection, $xlPivotTableVersion15)
$PivotTable.CreatePivotTable("R1C15","Tables1") | Out-Null 

[void]$ExcelWorksheet.Select()
$ExcelWorksheet.Cells.Item(1,15).Select()
$ExcelWorkbook.ShowPivotTableFieldList = $true 

$PivotFields = $ExcelWorksheet.PivotTables("Tables1").PivotFields("ASSIGNEDEMPID")
$PivotFields.Orientation = $xlRowField
#$PivotFields.Orientation = $xlDataField
$PivotFields.Position = 1

$PivotFields = $ExcelWorksheet.PivotTables("Tables1").PivotFields("CREATEDTIME")
$PivotFields.Orientation = $xlRowField
#$PivotFields.Orientation = $xlDataField

$PivotFields = $ExcelWorksheet.PivotTables("Tables1").PivotFields("TELNO")
$PivotFields.Orientation = $xlRowField
$PivotFields.Orientation = $xlDataField

#### Pivot Table - End
2

Excel is not fun to code for. It uses an old .COM object and as you can tell you have to hard-code variable references that are defined in VBA. Best advice is to record a macro in Excel and do what you are attempting manually in Excel. Review the VBA code and try to replicate that in Powershell.

3

Okay. I will give that a try. I am not the best at reading VBA code so sure to have more questions.

Thanks.

4

I don’t have experiences with it and I even don’t know if it has the needed function but I think it’s worth trying. You could take a look at the module from Doug Finke
ImportExcel. I’ve heard it’s lacking proper documentation but not functions. :wink:

5

Thanks. I have checked that out, briefly, and I couldn’t find out how to do what I am looking for.

6

Hmmm … that’s a pity. I’m sorry.

If you haven’t done yet you still can try other forums like StackOverflow or Reddit or Microsoft.

7

With Rob’s advice (thanks), and help from this page:

I was able to get the grouping to work.

This works:

$PivotGroupRange = $Excel.Range(“O3”)
$periods = @($false, $false, $false, $true, $false, $false, $false)
$PivotGroupRange.Group($true, $true, [Type]::Missing, $periods)

Seems you specify the cell you want to group, that’s the “O3”, and then set the grouping with the $false’s and $true’s. The Day is the fourth “period” set to $true.

Thanks all.

1 Like
8

Cool.

Thanks for sharing. :+1:t4: