Want an EXCEL RECORDSET based on an EXCEL RANGE - The plot thickens!
Posted by bill on November 16, 2001 8:25 AM
Hello.
Using RECORDSETS in ACCESS, you can quickly copy a RECORDSET to an ARRAY.
I assume you can do the same in EXCEL.
So, if you were to define an EXCEL RECORDSET to a workbook RANGE, it seems plausable to use the RECORDSET METHOD:
.GetRows
Doing so would yeild something like:
Dim RS As Recordset
Dim lngLastRow As Integer
Dim lngRows as Integer
Dim varData as Variant
lngLastRow = Range("A65536").End(xlUp).Row
Set RS = Excel.Range("A2:B" & lngLastRow)
varData = RS.GetRows(lngLastRow) ' You CAN specify a number greater than the actual RANGE. GetRows will not crap out!
I get a TYPE MISMATCH using the Set Command.
Posted by Dank on November 16, 2001 8:48 AM
I get a TYPE MISMATCH using the Set Command.
If you want to copy an entire range to an array you simply say:-
vArray = Range("A1:H68")
In this case vArray would be a 2d array which you can then manipulate. To assign it back to a range do this:-
Range("A1:H68") = vArray
This is the quickest way to work with large amounts of data in Excel.
Regards,
Daniel.
Posted by bill on November 16, 2001 9:08 AM
COOOOOOOOLLLL!!!!!!
If you want to copy an entire range to an array you simply say:- vArray = Range("A1:H68") In this case vArray would be a 2d array which you can then manipulate. To assign it back to a range do this:- Range("A1:H68") = vArray This is the quickest way to work with large amounts of data in Excel. Regards, Daniel.
Posted by bill on November 16, 2001 9:48 AM
Hold on a sec...
Not...
Dim lngLastRow As Long
Dim astrOrgs() As Variant
lngLastRow = Range("GenLabels!A65536").End(xlUp).Row
astrOrgs = Range("GenLabels!A2:B" & lngLastRow)
I get an error: Can't assign to an array
Anything wrong here??
COOOOOOOOLLLL!!!!!!
Posted by bill on November 16, 2001 10:30 AM
Re: Hold on a sec...Revised
This instead:
Sub ThisArray()
Dim TheArray As Variant
TheArray = Range("A1:B10").Value
Range("C1:D10") = TheArray
End Sub
-------------------------------------
Seems you must use the VALUE property in the ARRAY declaration...
