Powershell, Excel, Pivot Table

This topic contains 2 replies, has 3 voices, and was last updated by  Steve Larson 3 years ago.

  • Author
    Posts
  • #19107

    Jeff Wilson
    Participant

    [b]Hello all, [/b]
    Well, I am stumped again...
    I have Pivot code i need to convert to Powershell, and I am just not getting it rights... so can anyone please help code this block for, Thanks in Advance.....

    This is Excel VB Code....
    [blockquote]{Sub XX()
    '
    ' XX Macro

    Sheets("PivotSheet").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Data!R2C2:R1680C10", Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="PivotSheet!R1C1", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion14
    Sheets("PivotSheet").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Submit Date")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Actual Completion Date")
    .Orientation = xlRowField
    .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Actual Completion Date"), _
    "Count of Actual Completion Date", xlCount
    Range("A8").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
    False, True, False, True)
    End Sub}[/blockquote]

    Here is the code converted to Powershell....
    [blockquote]{[void]PivotSheet.Select()
    $PivotRange = "Data!R2C2:J$lastrow"+'C10'
    $PivotTable = $WorkBook.PivotCaches().Create($xlDatabase,$PivotRange,$xlPivotTableVersion14)
    $PivotTable.CreatePivotTable("PivotSheet!R1C1","PivotTable1") | Out-Null
    Start-Sleep -Milliseconds 500
    [void]PivotSheet.Select()
    $WorkSheet2.activate() | Out-Null

    $Worksheet2.Cells.Item(1,1).Select()
    $WorkBook.ShowPivotTableFieldList = $true
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("State")
    $PivotFields.Orientation = $xlRowField
    $PivotFields.Position = 1
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Submit Date")
    $PivotFields.Orientation = $xlRowField
    $PivotFields.Position = 2
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Actual Completion Date")
    $PivotFields.Orientation = $xlRowField
    $PivotFields.Position = 3
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("State")
    $PivotFields.Orientation = $xlColumnField
    $PivotFields.Position = 1

    $PivotFields = $Worksheet2.PivotTables("PivotTable1").AddDataField($Worksheet2.PivotTables,
    ("PivotTable1").PivotFields("Actual Completion Date"), "Count of Actual Completion Date", $xlCount)

    $PivotFields.Range("A8").Select}[/blockquote]

  • #19208

    Rob Simmers
    Participant

    What's not working? Is it getting to certain point and failing? I do see the PivotSheet variable is missing the $:

    [void]PivotSheet.Select()

    should be

    [void]$PivotSheet.Select()

  • #19247

    Steve Larson
    Participant

    We would need more information about what you are expecting and what behavior you are seeing.
    In addition to what Rob mentioned, I have had no luck with Select when dealing with ranges or cells. It appears to act as a noop. If I stop execution of the script at the point of the Select and do the selection manually in the visible spreadsheet, I find everything else works for my scripts. That is not very useful though.

You must be logged in to reply to this topic.