Filter excel file based on multiple criteria and copy filtered contents to another sheet

Question

I have multiple excel files, I want to filter a column name ( Items) using multiple criteria and paste the contents into another excel file. Sample source excel file attached.

In this example, I want to filter using criteria "Rice", "Vegetables" and "Butter" and copy to another sheet.
I have come up with the below code, it completes however it does not copy anything.

=================================================================

================================================

$column = 2   
$sfile = "C:\Temp\FileA.xlsx"  
$criteria = "Rice","Vegetables","Butter"  
$xl = New-Object -ComObject Excel.Application  
$xl.Visible = $false  
$swb = $xl.Workbooks.Open("$sfile")  
$swb = $swb.Worksheets.Item(1)  
$srange = $swb.UsedRange  
$srange.EntireColumn.AutoFilter()  
$srange.AutoFilter($column, $criteria)  

Answer 1

My advice: don't use the Excel.Application COM object (or any other COM object, but especially anything related to MS Office) if you can. It's unlikely you'll find MS Office installed on a server, and many office apps are security problems waiting to happen.

Instead, install the ImportExcel module (Install-Module ImportExcel -Scope <your-choice-here>) and then do the work using PowerShell:

$criteria = "Rice","Vegetables","Butter"
$column = 'Items'
Import-Excel -Path c:\Junk\sourcecopy.xlsx |        # imports 1st worksheet by default
    ForEach-Object{
        if ($criteria -contains $_.$column){
            $_
        }
    } | Export-Excel -Path c:\junk\File1.xlsx

Answer 2

Please suggest how to accomplish this using the AutoFilter or AdvancedFilter method. Due to some limitations I cannot use the module you have suggested.

Answer 3

Any suggestions on this?

Answer 4

Any example on how to use the advanced filter for this case?

Answer 5

Any suggestions on this?