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

Sam White 26 Reputation points
2022-05-01T08:33:24.617+00:00

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.

198062-capture12.png

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)  
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
    2022-05-01T14:59:13.013+00:00

    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
    
    0 comments No comments

  2. Sam White 26 Reputation points
    2022-05-02T01:48:54.087+00:00

    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
    2022-05-05T06:45:32.693+00:00

    Any suggestions on this?

    0 comments No comments

  4. Sam White 26 Reputation points
    2022-05-11T04:00:35.617+00:00

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


  5. Sam White 26 Reputation points
    2022-05-14T02:50:43.397+00:00

    Any suggestions on this?

    0 comments No comments