I have a variant array.

and i want to create a pivot table with it in VBA.

this is the extract from the code which works.....but it needs a recordset to be assigened to pivotcache.....


Dim objPivotCache As Excel.PivotCache
Dim connStr As String
connStr = getConnectionString("MarketDataName", MarketDataUID, MarketDataPwd)

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = connStr
conn.CommandTimeout = 300

Dim rstRecordset As ADODB.Recordset
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = conn
rstRecordset.Open VBA.Trim(mySQL)

Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset


is there any way in which i can assign a variant (array) to the pivotcache.

Appriciate your help.