Pivotcache into an array

[mrhopko]

Hi

Is it possible to copy pivotcache data straight into an array without going via a pivottable (i.e show detail).
I've been looking at pivotcache.Recordset.getrows but am having no joy.
Does anybody know a generic solution?

I would be massively grateful for any solution

Kind regards

Mrhopko

 

[mrhopko]

I think i cracked the monkey.

• You have to use ADO (something i have never used before).
• Use the Pivot Cache connection string (modified slightly) and command
• Use the connection to define the ADODB connection
• Use the Command to define the ADODB Recordset
• Copy the Recordset to an array (NB: the record set requires transposing)

Example:

Sub Test2()

Dim cn As ADODB.Connection
Dim Robj As ADODB.Recordset
Dim CoStr As String
Dim StSql As String
Dim a As Variant

StSql = ActiveWorkbook.PivotCaches(1).CommandText
CoStr = Replace(ActiveWorkbook.PivotCaches(1).Connection, "ODBC;", "")

Set Robj = New Recordset
Set cn = New ADODB.Connection

With cn
    .CursorLocation = adUseClient
    .Open CoStr
    Set Robj = .Execute(StSql)
End With

a = Application.WorksheetFunction.Transpose(Robj.GetRows)
MsgBox a(1, 2)


End Sub

The example was used for connection to a SQL database where the connection did not use a DSN file.
The example is for a workbook (active) that has a pivotcache already.
I have the 'Microsoft ActiveX Data Objects 2.x Library' active because i read that you have to.



Any comments or improvements please add as this is the first time i have used the ADO fancifulness.