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, 11 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.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort