none
Iterate through PageField Items in Excel Pivot Table

    Domanda

  • The code below takes a CSV file (a table) and creates an excel file with pivot tables. I am having trouble iterating through all the PageField items with PowerShell. I know in VBA it is something like this, but i can't get this to translate to Powershell. Any ideas?

    VBA:

    Sub PivotPages()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
       Set pt = ActiveSheet.PivotTables.Item(1)
       For Each pf In pt.PageFields
         For Each pi In pf.PivotItems
           pt.PivotFields(pf.Name).CurrentPage = pi.Name
         Next
       Next pf
    End Sub

    PowerShell:

    $filePathcsv ="C:\My Documents\Raw Data\20120625.txt"
    $filePathxlsx ="C:\Raw Data\20120625.xlsx"
    #Create the COM object and set application settings
    $excel = New-Object -ComObject excel.application
    $excel.Visible = $False
    $excel.DisplayAlerts = $False
    #Open the CSV file in excel
    $workbook = $excel.Workbooks.Open($filePathcsv)
    #Rename SHeet
    $workbook.worksheets.Item(1).Name = "Table"
    #Create the Pivot Table
    $pivot = $workbook.PivotCaches().Create(1, $workbook.worksheets.Item(1).UsedRange)
    $pivot.CreatePivotTable("","AR") | Out-Null
    #Set the new sheets with pivot tables on them
    $pivotsheet = $workbook.worksheets.Item(1)   
    #Add items to the pivot table
    $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Category_Description")
    $pivotfields.Orientation=1
    $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Five")
    $pivotfields.Orientation=2
    $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Total")
    $pivotfields.Orientation=4 
    #Add the filter items
    $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Abbreviation")
    $pivotfields.Orientation=3
    #Part that does not work!!!
    foreach ($pivotfields in $pivot.PageFields)
        {
           foreach($pageitems in $pivotfields.PivotItems)
             {
              $pivotfields.CurrentPage =                   pageitems.Name             
                #Once i have the iteration
                #Rename sheet, copy sheet and continue
              }
        }
    $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
    #Finally move table to the front
    $workbook.worksheets.Item("Table").Move($workbook.worksheets.Item(1))
        
    $xlFormat = 51
    $workbook.SaveAS($filePathxlsx,$xlFormat)
    $workbook.Close()
    $excel.Quit()
    $excel.DisplayAlerts = $True
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

    martedì 26 giugno 2012 18:15

Risposte

  • In case anyone ever needs a work-around solution here it is. The Evaluation function in excel will use the formula to calculate unique values in a column

    #this will count the unique values in the column
    $rows = $sheettable.UsedRange.Rows.Count - 1
    $formula = "=SUM(IF(FREQUENCY(Table!AZ2:AZ" + $rows + ",Table!AZ2:AZ" + $rows + ")>0,1))"
    [int]$uniquevalues = $excel.Evaluate($formula)
    for ($i=1; $i -le $uniquevalues; $i++)
        {
            #sets the filter to the nth item
            $workbook.worksheets.Item(1).PivotTables(1).PivotFields("Abbreviation").CurrentPage = $pivotfields.PivotItems($i).Value
            #set the worksheet name to the nth item
            $workbook.worksheets.Item(1).Name =$workbook.worksheets.Item(1).PivotTables(1).PivotFields("Abbreviation").PivotItems($i).Value
            #copies the worksheet to the front, if i am on the last loop do not do this
            if ($i -eq $uniquevalues) {break}
            $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
            $workbook.worksheets.Item(1).Activate()
        }

    • Contrassegnato come risposta Jay_Michael giovedì 28 giugno 2012 21:10
    giovedì 28 giugno 2012 21:09

Tutte le risposte

  • I changed my iteration to this, but the Count function is not counting any values. Was wondering if this change in code could spark any ideas?

    for ($i=1; $i -le $pivotfields.PivotItems.Count; $i++)
        {
            #sets the filter to the nth item
            $pivotfields.CurrentPage = $pivotfields.PivotItems($i).Value
            #set the worksheet name to the nth item
            $pivotsheet.Name = $pivotsheet.Cells.Item(1,2).Value()
            #copies the worksheet to the front
            $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
        }

    mercoledì 27 giugno 2012 14:51
  • In case anyone ever needs a work-around solution here it is. The Evaluation function in excel will use the formula to calculate unique values in a column

    #this will count the unique values in the column
    $rows = $sheettable.UsedRange.Rows.Count - 1
    $formula = "=SUM(IF(FREQUENCY(Table!AZ2:AZ" + $rows + ",Table!AZ2:AZ" + $rows + ")>0,1))"
    [int]$uniquevalues = $excel.Evaluate($formula)
    for ($i=1; $i -le $uniquevalues; $i++)
        {
            #sets the filter to the nth item
            $workbook.worksheets.Item(1).PivotTables(1).PivotFields("Abbreviation").CurrentPage = $pivotfields.PivotItems($i).Value
            #set the worksheet name to the nth item
            $workbook.worksheets.Item(1).Name =$workbook.worksheets.Item(1).PivotTables(1).PivotFields("Abbreviation").PivotItems($i).Value
            #copies the worksheet to the front, if i am on the last loop do not do this
            if ($i -eq $uniquevalues) {break}
            $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
            $workbook.worksheets.Item(1).Activate()
        }

    • Contrassegnato come risposta Jay_Michael giovedì 28 giugno 2012 21:10
    giovedì 28 giugno 2012 21:09