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.

 

[Fazza]

If I understand correctly, what you're seeing is standard behaviour?

 

[gmars]

No. standard behavior is for the row or column field data to display in the pivot table when they are added, even if there are no values fields selected.

 

[Fazza]

Are you sure?

I confirmed that what you describe is standard in Excel 2003 (the only version I use). I used the code from post #3 of http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html I set up a worksheet with headers & data, copied to give a second worksheet and ran the code. (There were only two worksheets in the file. I mention that cause the code was specifically written to make a pivot table from all worksheets. Also it has a connection string specific to Excel 2003, btw.)

What version are you using? Why do you think that what you're seeing is non-standard?

 

[gmars]

I am using Excel 2010. If I create a pivot table using the standard UI (not from code) and use data from the same workbook or connect to an external database, this problem does not occur.

 

[Fazza]

So standard behaviour like Excel 2003

 

[gmars]

I wouldn't call it standard. I would say the same quirk exists in 2003. Using an ADO recordset (at least with the code we both used) creates a pivot table that does not behave in the same manner as creating it any other way. I have also confirmed that creating the pivot table from code using a connecton string instead of an ADO recordset does not cause the problem. It only happens when using the ADO method. I am hoping that someone has found a workaround for this issue.

 

[Fazza]

Sounds like you have a workaround - modify to a method that gives the result you want.

This thread might disappear in the mass of postings pretty quickly, so if you're still after input from others suggest you wait some time - say til next week - and ask the question afresh. Include a link here for info. When the thread is unanswered it will be more prominent.

regards

 

[gmars]

Unfortunately, I have to use ADO since my query uses parameters. I will repost next week. Thanks for the tip.

Regards.

 

[Fazza]

Keep ADO! (I'm a big fan of ADO ) Load the recordset (headers &) data to a worksheet and make the pivot table from that - so it will be different from making the pivot table from the recordset. Could be a temporary, or hidden, worksheet and delete when done; or re-use with new data for each pivot table; or whatever. Or a temporary file.