Ouptut from object Recordset (ADO)

AnneOlga · March 8th, 2006, 07:32 AM

Hi!
I have the following questions

I) how can attribute the result from a query to an array?

Code:

Sheets(shtData).ActiveCell.CopyFromRecordset rsData

works fine to output in a cell but I would need something among the lines

Code:

Dim myArr() As Variant

Code:

myArr <- rsData '<- attribution of values

II) I also would like to parameter the output of a query on a given range but the following gives me an error (propriety or method not available for this object)

Code:

Dim rng As Range
Set rng = Range(Cells(2, nbCol), Cells(2, nbCol))
  Sheets(shtData).rng.CopyFromRecordset rsData

I guess there is something with the range property I misunderstood. Any idea?
Thanks

Anne-Olga

jrogers · March 30th, 2006, 06:17 AM

(I)
There is no quick feature to do this (such as copyfrom recordset) but it can be done. My question is why do you want to move it into an array, when you can manipulate the recordset object to return the data held within it

Code:

Dim varRecordset() As Variant

... code to open recordset

ReDim varRecordset(rs.RecordCount - 1, rs.Fields.Count - 1)
Do Until rs.EOF
   For a = 0 To rs.Fields.Count - 1
       varRecordset(rs.AbsolutePosition - 1, a) = rs(a)
    Next
    rs.MoveNext
Loop

(II)
I'm sure someone else can come up with something better tha this, but the code below works for me

Code:

Dim rng As Range
Sheets(shtdata).Select
Set rng = Range(Cells(2, nbCol), Cells(2, nbCol))

 rng.CopyFromRecordset rsData