Thank you for the response . . .

I was able to get to the point where I need to set my pivot-table - I think
there is something wrong with my destination -- this is from setting the
cache, loading the cache and setting my pivot . . .

Set jCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
Set jCache.Recordset = jRecordset
Set jPivot = jCache.CreatePivotTable(tabledestination:=jSheet.C ells(2,
2), tablename:="AssetRoll")

I know there is something wrong with when I set the pivot-table becaues it
left the tabledestination w/o the caps. jPivot is defined as a pivottable,
jCache is defined as a pivotcache.

Is there something simple I am missing?

Thank you . . .

"Héctor Miguel" wrote:

hi, !

I am trying to create a pivot table from a dynamic query
- so I create the query in VBA and then want to create a pivot table with that query.
What I am trying to do is to first create the pivotcache.
I am pretty lost on this one
- I can create a cache with Excel data, but am having a hard time creating it from a recordset.
Any ideas?

- set and open your connection-object
- set your recordset-object
- build the query-string
- open the recordset-object with your connection and query-string
- set / add your pivot-cache (sourcetype should be external)
- set your pivot-cache recordset as your opened recordset-object
- set your pivot-table (pivot-cache and destination)
- add (according your needs) the pivot-fields (rowfields, pagefields, pivotfields, ...)
- arrange the orientation (datafields, columnfields, ...), position, function, number format, etc..
- close your objects (recordset and connection)
- it's done !

hard to provide an "useful" example wothout your "real source data/needs/query/..." -???-

hth,
hector.