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

Question

question

SamWhite-6017 asked May 1, '22 SamWhite-6017 edited May 15, '22

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

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)

windows-server-powershell office-scripts-excel-dev

capture12.png (8.0 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-01T14:59:13.013Z

RichMatheisen-8856 answered May 1, '22 RichMatheisen-8856 edited May 1, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-02T01:48:54.087Z

SamWhite-6017 answered May 2, '22 RichMatheisen-8856 commented May 2, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · May 02 at 07:18 PM

I'd look for answers to your problem using visual basic and then convert them to PowerShell. Try a search with "excel visual basic autofilter". I expect you're missing the XlAutoFilterOperator -- but I don't know that much about using Excel to say that authoritatively.

0 ·

Answer 3 · 2022-05-05T06:45:32.693Z

SamWhite-6017 answered May 5, '22

Any suggestions on this?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-05-11T04:00:35.617Z

SamWhite-6017 answered May 11, '22 RichMatheisen-8856 edited May 14, '22

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

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · May 11 at 07:29 PM

That's more of an Excel question than a PowerShell one. However, wouldn't it look something like this:

 .AutoFilter( 2, Criteria1:=Array("Rice","Vegetables","Butter"), Operator:=xlFilterValues)

0 ·

SamWhite-6017 RichMatheisen-8856 · May 13 at 01:16 AM

I meant to ask how we can use Autofilter for excel from within powershell for filtering on three or more conditions.
The example you gave is for vba and does not work in powershell.

0 ·

RichMatheisen-8856 SamWhite-6017 · May 14 at 03:01 PM

I didn't say it would work in PowerShell, I asked if it should look like that. Note that there are three arguments, not just the two in your code.

 .AutoFilter( 2, $criteria, 7)

excel.range.autofilter
excel.xlautofilteroperator

0 ·

Answer 5 · 2022-05-14T02:50:43.397Z

SamWhite-6017 answered May 14, '22

Any suggestions on this?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question