none
customize pivot table using powershell RRS feed

  • Question

  • I am trying to automate a process that creates pivot tables. So far I am able to successfully create the pivot table but further I need to do the following:

    - Pivot table Options > Display > Select "Classic Pivot table layout..."

    - Design > Report Layout > Repeat All Item labels

    As of now, I can achieve the "Classic Pivot table layout" by specifying xlPivotTableVersion10 but the output slightly differs from what the client wants.

    I would like to find out if it is even possible to perform this via PowerShell. If so, how would I go about it.

    Thursday, March 1, 2018 2:09 AM

Answers

  • Hello abrakh123,

    Please check if below code could work for you.

    $excel= New-Object -ComObject Excel.Application
    $excel.visible=$true
    $wb=$excel.workbooks.open("C:\Users\Administrator\Desktop\111.xlsx")
    $ws=$wb.sheets("Sheet2")
    $piv=$ws.pivottables(1)
    $piv.InGridDropZones = $True
    $piv.RowAxisLayout(1)
    $piv.RepeatAllLabels(2)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by abrakh123 Friday, March 2, 2018 3:37 PM
    Friday, March 2, 2018 1:58 AM

All replies

  • Hello abrakh123,

    Please check if below code could work for you.

    $excel= New-Object -ComObject Excel.Application
    $excel.visible=$true
    $wb=$excel.workbooks.open("C:\Users\Administrator\Desktop\111.xlsx")
    $ws=$wb.sheets("Sheet2")
    $piv=$ws.pivottables(1)
    $piv.InGridDropZones = $True
    $piv.RowAxisLayout(1)
    $piv.RepeatAllLabels(2)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by abrakh123 Friday, March 2, 2018 3:37 PM
    Friday, March 2, 2018 1:58 AM
  • Terry - It works, that's exactly what I was looking for. Thanks alot!

    Friday, March 2, 2018 3:39 PM