create a new sheet using showdetail on pivottabes field in Powershell + VBA

Question

Double Clicking on Pivot tables GrandTotal data creates a separate sheet with entire data source. Would like to get this done through powershell. Below is the code I tried with Powershell 5.1.

$excelFile = "C:estTestfile.xlsb"
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)
$s=$wb.worksheets(1).range("C7").select
$s.showdetail
$wb.saveas("C:estTestfile_modified.xlsb")
$wb.close()
$Excel.quit()

question from:https://stackoverflow.com/questions/66049485/create-a-new-sheet-using-showdetail-on-pivottabes-field-in-powershell-vba

Answer 1

More direct - no need for select or intermediate variable $s:

$excelFile = "C:esterTestfile.xlsb"
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)
$wb.worksheets(1).range("C7").showdetail = $true
$wb.saveas("C:esterTestfile_modified.xlsb")
$wb.Close()
$Excel.Quit()
#make sure Excel process is ended (or it may persist)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

in a script file called from cmd using:

 powershell -noexit  "& ""C:TesterpsExcelTest.ps1"""