@VenkatB
Please follow the suggested steps and utilize the provided PowerShell scripts:
Ensure to set up the filename of your Excel sheet and the name of the pivot sheet, along with the field you intend to apply as a filter in the pivot table, as illustrated in the screenshot below.
Full code:
SET ExcelFileName TO $'''\"C:\\Deenu\\test_excel.xlsx\"'''
SET Pivot_Sheetname TO $'''\"Sheet2\"'''
SET Filter_FieldName TO $'''\"Jawatan\"'''
@@copilotGeneratedAction: 'False'
Scripting.RunPowershellScript.RunPowershellScript Script: $'''# Load Excel application
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true # Set to $true if you want Excel to be visible
# Open the workbook
$workbook = $excel.Workbooks.Open(%ExcelFileName%)
$worksheet = $workbook.Sheets.Item(%Pivot_Sheetname%)
# Get the pivot table
$pivotTable = $worksheet.PivotTables(1)
# Add \"Nama\" as a row filter
$pivotField = $pivotTable.PivotFields(%Filter_FieldName%)
$pivotField.Orientation = [Microsoft.Office.Interop.Excel.XlPivotFieldOrientation]::xlRowField
# Save and close the workbook
$workbook.Save()
$workbook.Close()
# Quit Excel
$excel.Quit()
# Clean up COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($pivotTable) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable pivotTable, worksheet, workbook, excel''' ScriptOutput=> PowershellOutput
How to copy/paste the above into you PAD?
Thanks,
Deenuji Loganathan 👩💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥
-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏
