Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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 - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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:=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? __ previous __ 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/..." -???- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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" wrote: 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:=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? __ previous __ 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/..." -???- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
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 __ 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:=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? __ previous __ 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/..." -???- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a pivottable from a recordset?
Thank you -- I appreciate the help.
"Héctor Miguel" wrote: 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 __ 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:=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? __ previous __ 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/..." -???- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem creating PivotTable from ADO recordset using VBA | Excel Programming | |||
PivotTable from a ADODB.Recordset ? | Excel Programming | |||
Create a Pivottable in a macro | Excel Discussion (Misc queries) | |||
create recordset from csv file on web | Excel Programming | |||
Create a PivotTable from a VBA array? | Excel Programming |