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

Answer 1

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)