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

Sam White 26 Reputation points

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.AutoFilter($column, $criteria)  
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,698 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,448 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Rich Matheisen 45,671 Reputation points

    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
            if ($criteria -contains $_.$column){
        } | Export-Excel -Path c:\junk\File1.xlsx
    0 comments No comments

  2. Sam White 26 Reputation points

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

  3. Sam White 26 Reputation points

    Any suggestions on this?

    0 comments No comments

  4. Sam White 26 Reputation points

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

  5. Sam White 26 Reputation points

    Any suggestions on this?

    0 comments No comments