My brain is toast today. I used to know how to do this. I have a recordset with 3 fields, and need to store them in an array. If I have, say 10 records, how do I declare the array and populate it with the field's data?
Thanks,
James
My brain is toast today. I used to know how to do this. I have a recordset with 3 fields, and need to store them in an array. If I have, say 10 records, how do I declare the array and populate it with the field's data?
Thanks,
James
"All that's necessary for evil to triumph is for good men to do nothing."
This is quick and dirty
Dim RowBuf As Variant Dim DisRS As rdoResultset Set DisRS = cn.OpenResultset(SQL, rdOpenForwardOnly) RowBuf = DisRS.GetRows(50)
Last edited by Aussiebear; 04-29-2023 at 10:05 PM. Reason: Adjusted the code tags
Here is something I threw together, there is probably a better way.
If you had data in A1:C something then this would fill A1:C1. then A2:C2 etc. into the array.Option Base 1 Option Explicit Sub Test() Dim x As Long Dim y As Long Dim z As Long Dim MyArrays(10, 10, 10) As String Dim MyCol As Long Dim MyRow As Long MyCol = 1 MyRow = 1 For x = 1 To 10 For y = 1 To 10 For z = 1 To 10 MyArrays(x, y, z) = Cells(MyRow, MyCol).Text If MyCol = 3 Then MyCol = 1 Else MyCol = MyCol + 1 End If If MyRow = Range("A65536").End(xlUp).Row Then GoTo z: Else MyRow = MyRow + 1 End If Next z Next y Next x z: End Sub
But this data is hard to visualize (for me at least). If I had three record sets, I would use 3 seperate Arrays to fill the data.
Last edited by XL-Dennis; 07-14-2004 at 02:16 AM. Reason: Added End Sub
Hi Jacob,
Good to see that You challenge arrays
If we don't insist to use an array of a specific datatype then I would prefer to use the following approach:
Option Explicit Sub Variant_Array() Dim wsSheet As Worksheet Dim rnData As Range Dim vaData As Variant Dim i As Long, j As Long, k As Long Set wsSheet = ActiveSheet With wsSheet Set rnData = .Range(.Range("A1"), .Range("C65536").End(xlUp)) End With 'Read all data into the array. vaData = rnData.Value 'Variant-arrays that reads data from a range is always 1-based. Debug.Print LBound(vaData) Debug.Print UBound(vaData) 'Here we make a dummy addition just to loop through the array: For j = 1 To 3 For i = 1 To UBound(vaData) vaData(i, j) = vaData(i, j) * 2 Next i Next j 'Read the revised data back to the range. rnData.Value = vaData End Sub
Hi Tommy,
Long time since I saw a RDO-approach as most people nowdays prefer ADO
Thank you for reading my post on this subject,
Dennis
Last edited by Aussiebear; 04-29-2023 at 10:09 PM. Reason: Adjusted the code tags