Excel Filter with Powershell

Welcome Forums General PowerShell Q&A Excel Filter with Powershell

This topic contains 1 reply, has 2 voices, and was last updated by

1 year, 7 months ago.

  • Author
  • #62803

    Points: 0
    Rank: Member

    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
    $xl.Visible = $true
    #Column 1
    $xlFilterValues = "" # This gives me all the blanks of the column

    #Column 2
    $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
    $xl.quit() #>

  • #69406

    Points: 22
    Rank: Member


    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.