将多维数组传递给 VBA 中的记录集变量

问题描述

我有一个数组，最终我想用它作为我的数据源来构建数据透视表，我知道数据透视表不会接受数组，我应该使用记录集作为数据透视表会接受这个.

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?

或

如何在数据透视表中使用我的数组数据?

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

感谢您的帮助.

推荐答案

您要查找的术语是断开连接的记录集".一旦你知道谷歌的话，就会有很多例子.例如:https://developer.rhino3d.com/guides/rhinoscript/disconnected-记录集排序/

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/

这是一个基本示例:

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

这篇关于将多维数组传递给 VBA 中的记录集变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！