Create a pivottable from a recordset?

[Rubble]

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?

Thank you -

 

[Héctor Miguel]

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.

 

[Rubble]

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:=xlExternal)
Set jCache.Recordset = jRecordset
Set jPivot = jCache.CreatePivotTable(tabledestination:=jSheet.Cells(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]

hi, !

your code is working just fine (for me), the only "pending actions" (if your code ends like this one)
is finish the pivot-table layout (it means, place each fields where it belongs to) so,
if you do not finish by code, you will have to (or let the user) finish in the GUI

- activate the pivot-table (select any cell within your PT area)
(be sure the PT toolbar shows-up and there will be available all of your button/fields)
- place each button/field where it is needed (which one/s as rowfields, datafields, columnfields, pagefields)
- change (if necessary) the function (count, sum, other...), numberformat, ... for the datafields

if any doubts (or further information)... would you please comment ?
hth,
hector.

__ OP __

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:=xlExternal)
Set jCache.Recordset = jRecordset
Set jPivot = jCache.CreatePivotTable(tabledestination:=jSheet.Cells(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?

__ previous __

 

[Rubble]

Thank you again for your response. My code actually stops at the set pivot
line - do I need to pick up some references to run those commands? Or is
this because I am on Vista or 2007?

Thanks again --

Jim

 

[Héctor Miguel]

hi, Jim !

... My code actually stops at the set pivot line
- do I need to pick up some references to run those commands?
Or is this because I am on Vista or 2007?

I have no win-vista, but testing on xl-2007 stil works (for me) ending on set pivot table line -?-
(no additional references, other than required for the connection/recordset objects)

after that, I "have to" finish the PT layout on the GUI (as previoulsy stated):

- activate the pivot-table (select any cell within your PT area)
- be sure the PT toolbar (pane in xl2007) shows-up and there will be available all of your button/fields
- place each button/field where it is needed (which one/s as rowfields, datafields, columnfields, pagefields)
- change (if necessary) the function (count, sum, other...), numberformat, ... for the datafields

hth,
hector.

__ previous __

 

[Rubble]

Thank you -- I appreciate the help.

hi, Jim !


I have no win-vista, but testing on xl-2007 stil works (for me) ending on set pivot table line -?-
(no additional references, other than required for the connection/recordset objects)

after that, I "have to" finish the PT layout on the GUI (as previoulsy stated):

- activate the pivot-table (select any cell within your PT area)
- be sure the PT toolbar (pane in xl2007) shows-up and there will be available all of your button/fields
- place each button/field where it is needed (which one/s as rowfields, datafields, columnfields, pagefields)
- change (if necessary) the function (count, sum, other...), numberformat, ... for the datafields

hth,
hector.

__ previous __