0

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:\test\Testfile.xlsb"
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)
$s=$wb.worksheets(1).range("C7").select
$s.showdetail
$wb.saveas("C:\test\Testfile_modified.xlsb")
$wb.close()
$Excel.quit()
Ishwar
  • 13
  • 1
  • Oh, but why? You can run VBA macros from the command line. Just get it working in Excel VBA, then call it from PowerShell if you must. Example here: https://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file – HackSlash Feb 04 '21 at 16:35
  • What happened when you tried that? Start by dropping the `Select` here: `$s=$wb.worksheets(1).range("C7").select` Select does not return a Range object. – Tim Williams Feb 04 '21 at 17:08
  • @TimWilliams I am not sure my code is good or not but when I visually did and recorded macro, it gave me below three commands. Range("C7").Select Selection.ShowDetail = True ActiveWorkbook.Save – Ishwar Feb 04 '21 at 17:33
  • Macro recorder is OK for getting some basic syntax but does not produce great code. You do not need `Select` there. – Tim Williams Feb 04 '21 at 17:39

1 Answers1

0

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

$excelFile = "C:\tester\Testfile.xlsb"
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)
$wb.worksheets(1).range("C7").showdetail = $true
$wb.saveas("C:\tester\Testfile_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:\Tester\psExcelTest.ps1"""
Tim Williams
  • 154,628
  • 8
  • 97
  • 125