将通过ADO查询得到的RecordSet数据集,保存到数组:
Function RecordsetToArray(rs As Object) As Variant
Dim results() As Variant
Dim headers() As Variant
Dim data As Variant
Dim i As Long, j As Long
' 检查记录集是否为空
If rs Is Nothing Then
RecordsetToArray = Empty
Exit Function
End If
' 检查记录集是否有记录
If rs.BOF And rs.EOF Then
RecordsetToArray = Empty
Exit Function
End If
' 获取字段名称作为标题行
ReDim headers(0 To rs.Fields.Count - 1)
For i = 0 To rs.Fields.Count - 1
headers(i) = rs.Fields(i).Name
Next i
' 使用GetRows方法将数据保存到数组中
data = rs.GetRows()
' 将标题和数据合并到一个数组中
ReDim results(LBound(data, 2) To UBound(data, 2) + 1, LBound(headers) To UBound(headers))
For i = LBound(headers) To UBound(headers)
results(0, i) = headers(i)
Next i
For i = LBound(data, 2) To UBound(data, 2)
For j = LBound(data, 1) To UBound(data, 1)
results(i + 1, j) = data(j, i)
Next j
Next i
' 返回结果数组
RecordsetToArray = results
End Function