I was trying filter option in Microsoft Excel via powerShell.
According to the requirement, I need to filter out all the values which are non-blank from 2 of the columns.
There are 10 rows in 2 of the columns of a workbook. Out of which 3 rows are blanks and 7 are non-blanks in each column. My requirement is to get those 7 rows, eliminating the blanks.
The code developed so far, only gives me the exact filter value i.e blanks, but I need the all the non blanks from 2 columns.
$oldfile = "xyz.xlsx" # source's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb1 = $xl.workbooks.open($oldfile) # open target
$sh1 = $wb1.sheets.item('Sheet1') # sheet in workbook
$xl.Visible = $true
$xlFilterValues = "" # This gives me all the blanks of the column
$xlFilterValues1 = "Class" # This gives me all the blanks of the column
$copyrange = $sh1.Range(“A1:V1”).EntireColumn
$copyrange.copy() | Out-Null
$wb1.close($true) # close and save workbook
Why are you using Excel COM object? This might be unstable since it's basically spawning an Excel instance in the background. I would use OleDB instead and you can filter what you want using standard SQL syntax.
The topic ‘Excel Filter with Powershell’ is closed to new replies.