title: Strange behavior in Excel with pivotable
body: <p>First I hope you will be able to understand my English :)</p>
  <p>And I thank you in advance for reading my problem.</p>
  <p>I'm in trouble with one of my workbook (Excel 2013).</p>
  <p>I can't give you the source but I'll try to explain.</p>
  <p>First I had a TCD built on a ListObject, a recordset fills the Listobject, which was the source of my pivottable. But sometimes there was too many lines for the listobject to handle...</p>
  <p>So to avoid this I switched to copying my Recordset in a pivotcache which was now used to create the pivottable. This to get rid of the 1M lines limitation of excel. This is working... sometimes.</p>
  <p>Sometimes Excel won't let me add a certain Pivotfield to my pivot table, let's call it &quot;SEQ&quot;, it says there are too many unique elements.</p>
  <p>But if I double clic on the grand total of my Pivottable, it details me the 260K lines which made the pivotcache in a new sheet (it's the same result of my sql query). And if I make a new pivottable on this range, then it's ok and I can add the field SEQ.</p>
  <p>That's odd ! I found this limitation about pivot table : Maximum number of unique items in a PivotTable -  1,048,576. But i don't have that many lines, and only 67K distinct SEQ !</p>
  <p>Why would Excel consider I've too many unique item when refering too pivotcache from a Recorset, but not when the pivot cache is based on a range in a worksheet ?</p>
  <p>I hope I was clear enough.</p>
  <p>Edit : Here is some code as asked</p>
  <p>Declared in an accessible module :</p>
  <p><code>Public cnn As New ADODB.Connection</code></p>
  <p><code>Public objcmd As New ADODB.Command</code></p>
  <p>In function that fetch the data and creat the PT :</p>
  <pre><code>Req = &quot;SELECT * FROM TABLE&quot;
  objcmd.CommandText = Req
  Set RS = objcmd.Execute
  Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  Set objPivotCache.Recordset = RS
  
  With objPivotCache
      .CreatePivotTable TableDestination:=Range(&quot;A8&quot;), TableName:=&quot;TCDDirect1&quot;
  End With
  
   With Sheets(&quot;Données&quot;).PivotTables(&quot;TCDdirect1&quot;).PivotFields(&quot;ETATR&quot;)
       .Orientation = xlRowField
       .Position = 1
   End With
   With Sheets(&quot;Données&quot;).PivotTables(&quot;TCDdirect1&quot;).PivotFields(&quot;FAMR&quot;)
       .Orientation = xlRowField
       .Position = 2
   End With
   With Sheets(&quot;Données&quot;).PivotTables(&quot;TCDdirect1&quot;).PivotFields(&quot;SEQ&quot;)  &lt;---- Program stop here in some cases
       .Orientation = xlRowField
       .Position = 3
   End With
   With Sheets(&quot;Données&quot;).PivotTables(&quot;TCDdirect1&quot;).PivotFields(&quot;LIBELLé&quot;)
       .Orientation = xlRowField
       .Position = 4
   End With
  </code></pre>