Powershell script to filter a particular item in excel

Question

I have created a Powershell script to filter out a particular item in an coulmn . The script so far :

$file1 = "C:\Users\ab270510\Desktop\t.xlsx" # source's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb1 = $xl.workbooks.open($file1) # open target
$sh1 = $wb1.sheets.item('Sheet1') # sheet in workbook
$sh1.Select()   
$sh1.Range("C1").Select() 
$xlFilterValues = 7 # found in MS documentation
$filterList = “Jan”,”feb” # array
$xl.Selection.AutoFilter(2, $filterList ,$xlFilterValues) 

$sh1.cells.Item.EntireColumn.AutoFit 

$wb1.close($true) # close and save  workbook
$xl.quit()

But the above code is giving error like : Exception calling "AutoFilter" with "3" argument(s): "AutoFilter method of Range class failed"

At line:1 char:25
 + $xl.Selection.AutoFilter <<<< (2, $filterList ,$xlFilterValues)
 + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
  + FullyQualifiedErrorId : ComMethodTargetInvocation

Please help me to write a Powershell script that will filter out only "Jan" & "Feb" item from a particular coulmn of an excel file . Also suggest me how can I filter single or multiple item .

Jeff W · Accepted Answer · 2014-08-28 15:55:05Z

1

Please see my answer here, after much reaserch i have figured this out, and it works.. Howerver, i am still working on how to do the reverse and only not choose a few items...Have to give credit to the above post... But i have tested and it works great...

Here is the working code, and a link to the thread...

# Filter for an array of values IE: choose muipliple items.... in one column $xlFilterValues = 7 # found in MS documentation $FL = @("value 1", "value2") $rng=$Worksheet.cells.item(2,38).entirecolumn $rng.select | Out-Null $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)

http://social.msdn.microsoft.com/Forums/office/en-US/81e4a2b0-d016-4a56-92e6-c3d4befa75db/powershell-excel-autofilter-on-multiple-items?forum=exceldev

edited Aug 28, 2014 at 15:55

answered Aug 28, 2014 at 15:02

Jeff W

136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell script to filter a particular item in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related