Shell script - set autofilter on a existing xls-file

Question

I want to set the filters on an existing .xls-file by running a shell script from the command line.

powershell -c "$excelObj = New-Object -ComObject Excel.Application;$excelWorkBook = $excelObj.Workbooks.Open(\"C:\Users\Desktop\Papierkorb\Test\test2.xlsx\");$excelWorkSheet = $excelObj.WorkSheets.item(\"Sheet1\");$excelWorkSheet.activate();$headerRange = $excelWorkSheet.Range(\"A1\",\"A1\").AutoFilter() | Out-Null;$excelWorkBook.Save();$excelWorkBook.Close();$excelObj.Quit()"

I am getting an error message:

Unable to get the AutoFilter property of the Range class

At line:1 char:231

I tried several adaptions with the Range, but could not fix it. Thanks for your help,

Answer 1

It is not possible to set AutoFilter on a range without data.

Try to put some text into cell "A1" in the test2.xlsx file (either using Excel, or problematically with PowerShell, example below). You can even put empty string ''.

The following works for me.

$excelObj = New-Object -ComObject Excel.Application;$excelWorkBook = $excelObj.Workbooks.Open("d:\temp\test2.xlsx");
$excelWorkSheet = $excelObj.WorkSheets.item("Sheet1");
$excelWorkSheet.activate();
$headerRange = $excelWorkSheet.Range("A5","A5") ;
$headerRange.Item(1,1) = 'Something'
$headerRange.AutoFilter() ;
#$headerRange.AutoFilter() | Out-Null;
$excelWorkBook.Save();
$excelWorkBook.Close();
$excelObj.Quit()