title: Adding multiple column fields to a PivotTable with Powershell
body: <p>I am having trouble adding multiple column fields to a PivotTable in Excel using Powershell. The recorded VBA macro works fine, but once transcribed to Powershell, subtle errors appear.</p>
  
  <p>I have three columns: ID, Name, and Group. I want ID and Name to be the column groupings and Group to be the row grouping.</p>
  
  <p>This can be accomplished in the Excel interface without any issues or errors. The macro recorded is:</p>
  
  <pre><code>With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
      .Orientation = xlColumnField
      .Position = 1
  End With
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("ID")
      .Orientation = xlColumnField
      .Position = 2
  End With
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("Group")
      .Orientation = xlRowField
      .Position = 1
  End With
  </code></pre>
  
  <p>The macro recorded runs and works perfectly. Now, I need to replicate this code in Powershell for an automated job. Here's the Powershell I wrote:</p>
  
  <pre><code>$pt.PivotFields('Name').Orientation = [Microsoft.Office.Interop.Excel.XlPivotFieldOrientation]::xlColumnField
  $pt.PivotFields('Name').Position = 1
  $pt.PivotFields("ID").Orientation = [Microsoft.Office.Interop.Excel.XlPivotFieldOrientation]::xlColumnField
  $pt.PivotFields("ID").Position = 2
  $pt.PivotFields('Group').Orientation = [Microsoft.Office.Interop.Excel.XlPivotFieldOrientation]::xlRowField
  $pt.PivotFields('Group').Position = 1
  </code></pre>
  
  <p>Even though the code is identical, the results are not. With the VBA macro, the Pivot Table ends up with Name and ID as column fields and Group as the row field. With the Powershell script, only the Name is made a column field. The ID is not made a column field. Group still becomes a row field.</p>
  
  <p>Any ideas? I've also tried using this approach with a different API method:</p>
  
  <pre><code>$pt.AddFields('Group', [array]('Name','ID'))
  </code></pre>
  
  <p>This gives the same result.</p>