passing a multidimensional array into a recordset variable in VBA

Question

I have an array that ultimatley i would like to use as my data source to build a pivot table, I understand that a pivot table wont accept an array and that I should use recordset as pivot will accept this.

I am struggling to build a recordset using my array.

how do I pass a multidimensional array into a recordset?

or

how do I use my array data in a pivot table?

Thanks for any help.

Answer 1

The term you're looking for is "disconnected recordset". Plenty of examples out there once you know the words to Google. Eg: https://developer.rhino3d.com/guides/rhinoscript/disconnected-recordset-sorting/

Here's a basic example:

Sub PopCache()

    Dim pc As PivotCache, rs As ADODB.Recordset, pt As PivotTable
    Dim arr, x As Long

    Set pc = ThisWorkbook.PivotCaches.Create(xlExternal)

    'get an array for testing
    arr = ActiveSheet.Range("J4").CurrentRegion.Value 

    'populate the pivotcache from a recordset
    Set pc.Recordset = ArrayToRecordSet(arr, _
                                        Array("Name", "Color", "Length"))

    Set pt = pc.CreatePivotTable(ActiveSheet.Range("B2"))

End Sub

'Take an array of data and an array of field names and
'  return a populated disconnected recordset
Function ArrayToRecordSet(arr, fieldNames) As Object
    Dim rs As Object, r As Long, c As Long, h, f As Long

    Set rs = CreateObject("ADODB.Recordset")
    For Each h In fieldNames
        rs.Fields.Append h, adVariant
    Next h
    rs.Open
    For r = LBound(arr, 1) To UBound(arr, 1)
        rs.AddNew
        f = 0
        For c = LBound(arr, 2) To UBound(arr, 2)
            rs.Fields(f).Value = arr(r, c)
            f = f + 1
        Next c
    Next r
    Set ArrayToRecordSet = rs
End Function