Author Posts

September 25, 2014 at 11:33 am

[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]

September 30, 2014 at 5:30 am

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()

October 1, 2014 at 5:39 am

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.