title: Powershell Excel PivotFilters.Add
body: <p>I've written a Powershell script to grab a CSV, create an Excel sheet from it, and create a Pivottable with a few rows and summary values.  I'm also able to add a filter field but what I cannot successfully do is tell the filter field what value I want it to filter on.</p>
  
  <p>The PivotFilters.Add and PivotFilters.Add2 methods always throw a "Value does not fall within the expected range" error.  </p>
  
  <p>Here's the relevant portions of the code:</p>
  
  <pre><code># Add PivotTable
  $WS2 = $WBK1.Worksheets.Add()   
  $WS2.Name = "PivotTable"
  
  $xlPivotTableVersion15     = 5 # Excel 2013
  $xlPivotTableVersion12     = 3 # Excel 2007
  $xlDescending              = 2
  $xlDatabase                = 1 # this just means local sheet data
  $xlHidden                  = 0
  $xlRowField                = 1
  $xlColumnField             = 2
  $xlFilterField             = 3
  $xlDataField               = 4    
  $xlSum                     = -4157
  $xlAverage                 = -4106
  $xlCount                   = -4112
  $xlRight                   = -4152
  
  $pivotRange = $WS1.UsedRange    
  
  $PivotTable = $WBK1.PivotCaches().Create($xlDatabase,$pivotRange,$xlPivotTableVersion15)
  $PivotTable.CreatePivotTable("R1C1","Tables1") | Out-Null 
  [void]$WS2.Select()
  
  $WBK1.ShowPivotTableFieldList = $true 
  
  # configure pivottable fields
  
  $PivotFields = $WS2.PivotTables("Tables1").PivotFields("VmName")
  $PivotFields.Orientation = $xlRowField
  
  $PivotFields = $WS2.PivotTables("Tables1").PivotFields("RAM")
  $PivotFields.Orientation = $xlDataField
  $PivotFields.Function = $xlAverage
  
  $PivotFields = $WS2.PivotTables("Tables1").PivotFields("vCPU")
  $PivotFields.Orientation = $xlDataField
  $PivotFields.Function = $xlAverage
  
  $PivotFields = $WS2.PivotTables("Tables1").PivotFields("Dept") # I also tried commenting these lines out
  $PivotFields.Orientation = $xlFilterField                      # I also tried commenting these lines out
  
  $xlValueEquals = 7
  $xlCaptionEquals = 15
  $WS2.PivotTables("Tables1").PivotFields("Dept").PivotFilters.Add2($xlValueEquals, "Finance")
  </code></pre>
  
  <p>I have tried using both PivotFilters.Add and PivotFilters.Add2 either way I always get "Value does not fall within the expected range."</p>
  
  <p>I've also tried commenting out the lines annotated above as I wondered if already having a filter created was confusing it.  </p>
  
  <p>Any advice greatly appreciated!</p>