3

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...

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
RedRaven
  • 725
  • 2
  • 18
  • 33

1 Answers1

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)
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • 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? – RedRaven May 06 '13 at 12:59
  • 1
    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. – Tomalak May 06 '13 at 13:21
  • Makes sense, and one of the things I was trying was `AdvancedFilter(xlFilterInPlace, , , True)` and it wasn't working, now I know why. Thanks! – RedRaven May 06 '13 at 13:22