none
delete Pivot table from powershell RRS feed

  • Question

  • how to delete a pivot table in powershell?

    how to remove pivotfield in powershell ???

    Wednesday, December 10, 2014 9:30 AM

Answers

  • Hi Sakurai,

    Thanks for posting in MSDN forum.

    >>how to delete a pivot table in powershell?

    how to remove pivotfield in powershell ???<<

    We can use PivotTable Object to manipulate the Pivot table. And recrod macros in Excel can help us to familar with the Excel objet model. Also I wrote a sample using Powershell to demo delte the pivot table and remove pivot fileds for your reference.

    Remove pivot fields of PivotTable1 on the sheet3.

    $excelApp = New-Object -comobject Excel.Application
    
    $xlEnum = New-Object -TypeName PSObject
    $excelApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {
     
        # create properties from enum values
        $enum = $_
        $enum.GetEnumNames() | ForEach-Object {
            $xlEnum | Add-Member -MemberType NoteProperty -Name $_ -Value $enum::($_)
        }
    }
    
    $excelApp.Visible = $True
    $aWorkbook = $excelApp.Workbooks.open("C:\Users\UserName\Desktop\Book1.xlsm")
    for ($i=$aWorkbook.Worksheets.item("Sheet3").PivotTables("PivotTable1").PivotFields.Count;$i -gt 0;$i--)
    {
        $aWorkbook.Worksheets.item("Sheet3").PivotTables("PivotTable1").PivotFields($i).Orientation=$xlEnum.xlHidden 
    }
    
    
    Delete PivotTable4 on the sheet3
    $excelApp = New-Object -comobject Excel.Application
    
    
    $excelApp.Visible = $True
    $aWorkbook = $excelApp.Workbooks.open("C:\Users\UserName\Desktop\Book1.xlsm")
    $aWorkbook.Worksheets.item("Sheet3").PivotTables("PivotTable4").TableRange2.Delete()
    
    
    

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 12, 2014 5:26 AM
    Moderator