Auto Filter Excel Data and Export to new Sheet by Powershell

PowerShell Help
1

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
2

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

3

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