Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Pain!
Dear All
I am trying to automate the creation of a pivot table, whether from within Excel or through automation from another package and always seem to get an error when trying to load the ADO recordset data into the pivot cache (see sample code below). If I add a pivotcache to the pivotchaches collection I receive no error, but when I try to Set objPivotcache.Recordset = rstRecordset then I get an error. I am simply stumped. If the pivotcache object is added to the pivotcaches collection, then should each pivotcache not have an Index? Can anyone help me? Alastair Sub CreatePivot() On Error GoTo err_handler: Dim SQLString As String Dim cnnConn As ADODB.Connection Dim rstRecordset As ADODB.Recordset Dim cmdCommand As ADODB.Command Dim objPivotcache As Excel.Pivotcache 'RECORDSET OPENED AND POULATED WITH DATA Set objPivotcache = xlApp.ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlExternal) 'THIS LINE HERE IS WHERE THE 1004 AUTOMATION ERROR OCCURRS. Set objPivotcache.Recordset = rstRecordset With objPivotcache .CreatePivotTable TableDestination:=Range("B5"), _ TableName:="pivAccessNL" End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Pain!
Alastair
It worked for me from within Excel. I found a few other posts on google with the same problem, but alas, no answers. I would try it with a different recordset (the smaller and simpler) and see if there's a problem with the recordset (maybe size or complexity). If you get it to work with a small, simple recordset, maybe it will help isolate the problem. Sorry I couldn't be more help. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Alastair MacFarlane" wrote in message ... Dear All I am trying to automate the creation of a pivot table, whether from within Excel or through automation from another package and always seem to get an error when trying to load the ADO recordset data into the pivot cache (see sample code below). If I add a pivotcache to the pivotchaches collection I receive no error, but when I try to Set objPivotcache.Recordset = rstRecordset then I get an error. I am simply stumped. If the pivotcache object is added to the pivotcaches collection, then should each pivotcache not have an Index? Can anyone help me? Alastair Sub CreatePivot() On Error GoTo err_handler: Dim SQLString As String Dim cnnConn As ADODB.Connection Dim rstRecordset As ADODB.Recordset Dim cmdCommand As ADODB.Command Dim objPivotcache As Excel.Pivotcache 'RECORDSET OPENED AND POULATED WITH DATA Set objPivotcache = xlApp.ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlExternal) 'THIS LINE HERE IS WHERE THE 1004 AUTOMATION ERROR OCCURRS. Set objPivotcache.Recordset = rstRecordset With objPivotcache .CreatePivotTable TableDestination:=Range("B5"), _ TableName:="pivAccessNL" End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Pain!
Dick Kusleika
Thanks for the comments. The problem I have is that it works sometimes and more often errors at exactly the same point. So annoying. Programming can be like that sometimes. I thought that a possible problem could've been that an instance of excel would remain in memory and somehow the caches of the two instances of Excel could error. Unfortunately I am accessing excel on a remote pc and can't see the instances of excel stored on the pc. Thanks again. Alastair "Dick Kusleika" wrote in message ... Alastair It worked for me from within Excel. I found a few other posts on google with the same problem, but alas, no answers. I would try it with a different recordset (the smaller and simpler) and see if there's a problem with the recordset (maybe size or complexity). If you get it to work with a small, simple recordset, maybe it will help isolate the problem. Sorry I couldn't be more help. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Alastair MacFarlane" wrote in message ... Dear All I am trying to automate the creation of a pivot table, whether from within Excel or through automation from another package and always seem to get an error when trying to load the ADO recordset data into the pivot cache (see sample code below). If I add a pivotcache to the pivotchaches collection I receive no error, but when I try to Set objPivotcache.Recordset = rstRecordset then I get an error. I am simply stumped. If the pivotcache object is added to the pivotcaches collection, then should each pivotcache not have an Index? Can anyone help me? Alastair Sub CreatePivot() On Error GoTo err_handler: Dim SQLString As String Dim cnnConn As ADODB.Connection Dim rstRecordset As ADODB.Recordset Dim cmdCommand As ADODB.Command Dim objPivotcache As Excel.Pivotcache 'RECORDSET OPENED AND POULATED WITH DATA Set objPivotcache = xlApp.ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlExternal) 'THIS LINE HERE IS WHERE THE 1004 AUTOMATION ERROR OCCURRS. Set objPivotcache.Recordset = rstRecordset With objPivotcache .CreatePivotTable TableDestination:=Range("B5"), _ TableName:="pivAccessNL" End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Template Help Task Pane is being a pain! | Excel Discussion (Misc queries) | |||
Task Pain | Excel Discussion (Misc queries) | |||
Comments on frozen pain | Excel Discussion (Misc queries) | |||
Oh God the Pain! Help Please! | Excel Discussion (Misc queries) | |||
Is there any way to keep the Task Pane (pain) from displaying? | Excel Discussion (Misc queries) |