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:=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 __
>>>>> 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/..." -???-
|
Similar Threads
