Powershell, Excel, Pivot Table

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

  • Author
    Posts
  • #19107
    Profile photo of Jeff Wilson
    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
    Profile photo of Rob Simmers
    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
    Profile photo of Steve Larson
    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.