Auto Filter Excel Data and Export to new Sheet by Powershell

Welcome Forums General PowerShell Q&A Auto Filter Excel Data and Export to new Sheet by Powershell

This topic contains 2 replies, has 2 voices, and was last updated by  Lakshmana Shiva 6 months, 3 weeks ago.

  • Author
    Posts
  • #97106

    Lakshmana Shiva
    Participant

    I am having a Excel sheet with some data like Age of the person above 10-70 Years.I need to filter the data of above 15 from the excel and copy that new filtered to the new sheet in the same excel.When I trying that i am getting the error.Can anyone help me on this issue.

    I am having that data of above 20000 which trying to manual takes time so coming to Powershell to filter and export the data.

    $testfile = "C:\Folder\file.xlsx"
    $xl = New-Object -Com Excel.Application
    $xl.DisplayAlerts = $false
    $wb = $xl.Workbooks.Open($testfile)
    $sh1 = $wb.sheets.item('sheetname')
    $sh1.Select()
    $sh1.Range("G2").Select()
    $xlFilterValues = 15
    $FL = @("Age")
    $rng=$Worksheet.cells.item(1 , 28).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)
    $xl.workbooks.save
    $xl.visible=$false
    $xl.quit()

    True
    You cannot call a method on a null-valued expression.
    At C:\Folder\test.ps1:10 char:1
    + $rng=$Worksheet.cells.item(1 , 28).entirecolumn
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Folder\test.ps1:12 char:1
    + $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

  • #97188

    Emil Tanev
    Participant

    $Worksheet is null? Do you want to use $sh1?

  • #97193

    Lakshmana Shiva
    Participant

    I have tried this also but getting error

    $testfile = "C:\Folder\file.xlsx"
    $xl = New-Object -Com Excel.Application
    $xl.DisplayAlerts = $false
    $wb = $xl.Workbooks.Open($testfile)
    $sh1 = $wb.sheets.item('sheetname')
    $sh1.Select()
    $sh1.Range("G2").Select()
    $xlFilterValues = 15
    $FL = @("Age")
    $rng=$sh1.cells.item(1 , 28).entirecolumn
    $rng.select | Out-Null
    $xl.Selection.AutoFilter(20,$FL,$xlFilterValues)
    $xl.workbooks.save
    $xl.visible=$false
    $xl.quit()

    At line:12 char:1
    + $xl.Selection.AutoFilter(20,$FL,$xlFilterValues)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

The topic ‘Auto Filter Excel Data and Export to new Sheet by Powershell’ is closed to new replies.

Skip to toolbar