Excel Filter with Powershell

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Leandro Wajswajn Pereyra Leandro Wajswajn Pereyra 1 month ago.

  • Author
    Posts
  • #62803
    Profile photo of Aswin Kumar
    Aswin Kumar
    Participant

    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

    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.

You must be logged in to reply to this topic.