Author Posts

January 27, 2017 at 9:13 am

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.
For example:
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
$sh1.Select()
$xl.Visible = $true
#Column 1
$sh1.Range("J1").Select()
$xlFilterValues = "" # This gives me all the blanks of the column
$xl.Selection.AutoFilter(10,$xlFilterValues)

#Column 2
$sh1.Range("I1").Select()
$xlFilterValues1 = "Class" # This gives me all the blanks of the column
$xl.Selection.AutoFilter(9,$xlFilterValues1)

$sh1.cells.Item.EntireColumn.AutoFit

$sh1.activate()
$copyrange = $sh1.Range(“A1:V1”).EntireColumn
$copyrange.copy() | Out-Null
$wb1.close($true) # close and save workbook
$xl.quit() #>
[gc]::collect()
[gc]::WaitForPendingFinalizers()

April 24, 2017 at 3:48 pm

Hi,

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.