PowerShell中的高级过滤器 [英] Advanced filter in powershell

问题描述

 
 >  $ rangetofilter = $ worksheet2.usedrange.select 
 $ excel.selection.autofilter（2，TestFilter）
  

但是，我不明白如何正确转换这里给出的语法 http://msdn.microsoft.com/en-us/library/office/bb209640（v = office.12）.aspx Powershell会接受的东西。例如，我已经尝试了

  $ excel.selection.AdvancedFilter（xlFilterInPlace，，，TRUE ）
  

但会出现以下错误：

 使用4参数调用AdvancedFilter的异常：
范围类失败的AdvancedFilter方法
在行：1 char：32 
 + $ excel.selection.AdvancedFilter<<<< （xlFilterInPlace，，，TRUE）
 + CategoryInfo：NotSpecified：（:) []，MethodInvocationException 
 + FullyQualifiedErrorId：ComMethodTargetInvocation 
 那么有没有办法通过PowerShell运行一个Excel高级过滤器？
 
 
 
编辑 - 发现这个： http://gallery.technet.microsoft.com / ScriptCenter / 57b497a4-d634-44c6-be5c-ba2699f9961a / ，但它也不能正常工作...    解决方案

是一个字符串。



  Object AdvancedFilter（
 XlFilterAction Action，
 Object CriteriaRange，
 Object CopyToRange，
 Object Unique 
）
  

第一个是枚举。在VBA中，你可以直接使用它们，因为它们隐含在全局中。在Powershell中不是这样，你必须用完全限定的名字明确地引用它们：

$ $ $ $ $ $ $ $ $ $ $ $ $ $'$'$' Interop.Excel.XlFilterAction] :: xlFilterInPlace


其他三个参数的类型为 Object ，这意味着它们是COM中的 Variant 类型。然而，＃2和＃3应该是范围对象，所有投注都是关闭的，如果你通过别的东西。

他们也被标记为可选。可选参数应该没有值由 在.NET COM Interop中丢失键入。再次，在Powershell中你必须明确地引用它：

  $ missing = [Type] :: Missing 
 < 
 
 $ b 
参数＃4应该是一个布尔值，所以只需传递一个Powershell bool常量（或者，因为此参数也是可选的， $ missing ）。 
 
 
  $ excel.Selection.AdvancedFilter（$ xlFilterInPlace，$ missing，$ missing，$ TRUE）
  
 
I am trying to use the Excel advanced filter through Powershell but 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 here http://msdn.microsoft.com/en-us/library/office/bb209640(v=office.12).aspx to something that Powershell will accept. For example, I've tried 
$excel.selection.AdvancedFilter("xlFilterInPlace","","","TRUE")
but 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?


EDIT -- found this: http://gallery.technet.microsoft.com/ScriptCenter/57b497a4-d634-44c6-be5c-ba2699f9961a/ but it is also not working...
解决方案
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)


                        
这篇关于PowerShell中的高级过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！