Pivot table created from ADO recordset doesn't display Row or Column field data unless there is a Datafield

[gmars]

I have created a pivot table from an ADO recordset using VBA. The pivot table is created and everything seems normal. However when I start adding row or column fields, the title cells display in the sheet, but not the individual records. They records will not appear until I add a datafield to the Values area, then everything works properly. if you remove the datafield, all data in the table, except the titles, disappears. Any help would be greatly appreciated. Below is the code that assigns the recordset to the pivot cache.

Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set pvtCache.Recordset = myRecordSet
Set pvtTable = pvtCache.CreatePivotTable(ActiveSheet.Range("c3"), "Exhibit")

Thanks.

 

[gmars]

Solved! Turns out you were on the right track. The ADO code I was using creates a pivot table as xlPivotTableVersion2000. The other methods create it as xlPivotTableVersion14. To solve the problem you have to create both the pivot cache and the pivot table as version 14. This also requires changing the pivot cache create line from an "Add" to a "Create". The adjusted lines are below:


Set pvtCache = ActiveWorkbook.PivotCaches.Create(xlExternal, , xlPivotTableVersion14)
Set pvtCache.Recordset = myRecordSet
pvtCache.CreatePivotTable TableDestination:="TestSheet!R3C3", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

 

[Fazza]

well done

 

[jalozano]

Thanks gmars

I also had this problem!!ray:!!