Populating a Pivot Table Cache

Hi, QB.

Post #3 of http://www.mrexcel.com/forum/showthread.php?t=315768 shows a recordset being populated using ADO.

Then, the line "Set objPivotCache.Recordset = objRS" sets the pivotcache's recordset to that object, and the pivot table follows. Later in the thread, post #15 shows the pivot table code a bit more usefully.

OK? regards

Hi Fazza,

Many thanks for your reply. I did search for a solution but must have used the wrong key words. I'll look at the entire thread and try to develop the code later today. Could I get back to you if I have problems?

Regards

QB

Sure - though it is beer o'clock for me & I've just grabbed a 0.5L Weihenstephaner HefeWeißbier. I will not post again today.

In fact what you want to should be relatively straightforward. I don't know about the particular database, but basically query it from Excel VBA and populate a recordset. This might be covered adequately in your references, or google searches. Often the results would be pasted to a worksheet. For your situation though, and this is the trick, just set the pivotcache recordset = the one you have just created.

If you need, google for "connection strings" to find the particular connection string.

IIRC Excel 2003's help topic for recordset has example code that demonstrates the same idea. Have fun. Regards,