Advanced filter in PowerShell

Question

I am trying to use the Excel advanced filter through PowerShell, but I am not having any luck. I can use an autofilter successfully by running the following code:

$rangetofilter = $worksheet2.usedrange.select
$excel.selection.autofilter(2, "TestFilter")

However, I don't understand how to properly convert the syntax given in Range.AdvancedFilter Method to something that PowerShell will accept. For example, I've tried

$excel.selection.AdvancedFilter("xlFilterInPlace", "", "", "TRUE")

But I get the following error:

Exception calling "AdvancedFilter" with "4" argument(s): "AdvancedFilter method of
Range class failed"
At line:1 char:32
   + $excel.selection.AdvancedFilter <<<< ("xlFilterInPlace","","","TRUE")
   + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
   + FullyQualifiedErrorId : ComMethodTargetInvocation

So is there a way to run an Excel advanced filter through PowerShell?

I found this: Delete duplicate rows in excel using advanced filter, but it is not working either...

Tomalak · Accepted Answer · 2013-05-06 04:43:08Z

6

None of the arguments to AdvancedFilter() is a string.

Object AdvancedFilter(
    XlFilterAction Action,
    Object CriteriaRange,
    Object CopyToRange,
    Object Unique
)

The first is an enumeration. In VBA you can use those directly because there they are implicitly global. Not so in Powershell, where you have to reference them explicitly by their fully qualified names:

$xlFilterInPlace = [Microsoft.Office.Interop.Excel.XlFilterAction]::xlFilterInPlace

The other three arguments are typed as Object, which means they are of Variant type in COM. However, #2 and #3 are supposed to be Range objects, all bets are off if you pass in something else.

They are also marked as optional. Optional parameters that should have no value are represented by the Missing type in .NET COM Interop. Again, in Powershell you have to reference it explicitly:

$missing = [Type]::Missing

Argument #4 is supposed to be a Boolean, so just pass a Powershell bool constant (or, since this parameter is optional as well, $missing).

$excel.Selection.AdvancedFilter($xlFilterInPlace, $missing, $missing, $TRUE)

answered May 6, 2013 at 4:43

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RedRaven Over a year ago

Thank you for the clear and concise answer. This is why I love stackoverflow. My only other question is: why is it that for Advancedfilter I have to reference everything explicitly, while for Autofilter I do not?

Tomalak Over a year ago

Because the first argument for AutoFilter() accepts an Integer and the second argument accepts a String, which is exactly what you did. All other arguments are optional and you want to leave them at their default values, which means you can safely omit them. In the case of AdvancedFilter() you want to use a non-default value for the fourth argument, which means you have to specify #2 and #3. PowerShell does not support VBA's syntax for optional parameters (AdvancedFilter(xlFilterInPlace, , , True), which internally does the same thing: it passes "missing" for every omitted argument.

RedRaven Over a year ago

Makes sense, and one of the things I was trying was AdvancedFilter(xlFilterInPlace, , , True) and it wasn't working, now I know why. Thanks!

Collectives™ on Stack Overflow

Advanced filter in PowerShell

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related