Pivot Table Pain!

[Alastair MacFarlane]

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

 

[Dick Kusleika]

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.

 

[Alastair]

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