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

 
Participant
1 year, 11 months ago.

  • Author
    Posts
  • #62803

    Participant
    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
    $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()

  • #69406

    Participant
    Points: 22
    Rank: Member

    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.

The topic ‘Excel Filter with Powershell’ is closed to new replies.

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