Powershell, Excel, Pivot Table

Welcome Forums General PowerShell Q&A Powershell, Excel, Pivot Table

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
4 years, 5 months ago.

  • Author
    Posts
  • #19107

    Participant
    Points: 0
    Rank: Member

    Hello all,
    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....

    {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}

    Here is the code converted to Powershell....

    {[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}

  • #19208

    Participant
    Points: 639
    Helping Hand
    Rank: Major Contributor

    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

    Participant
    Points: 0
    Rank: Member

    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.

The topic ‘Powershell, Excel, Pivot Table’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort