Hi

How do I create a recordset from a range (A1:G100) or named range (Adress,
referes to A1:G100)?

I´ve tried

rs.open("SELECT * FROM Adress")

but what is the ActiveConnection?

Regards
PO

Are you sure that you want a recordset?

you can easily grab the data into an array

DIM MyData as Variant
MyData = Range("A1:G100")

If you REALLY want a recordset, then you have to build
it, then populate it...
On a worksheet
create a table with headers. say G3:L104 and range name
it MyData
The code uses the name, MyData, but the number of columns
and rows does't matter. I used 5x101 so long as the
column headers are all different.
In the IDE set a reference to the MS ActiveX Data Obkects
2.7 Library

In a module add:-

Option Explicit
Dim rst As Recordset

Sub main()
Build_RST
Show_RST
End Sub


Sub Build_RST()
Dim cl As Long, rw As Long
Dim Source As Range

Set Source = ThisWorkbook.Names("MyData").RefersToRange

Set rst = New Recordset

With rst
With .Fields
For cl = Source.Column To (Source.Columns.Count +
Source.Column - 1)
.Append Cells(Source.Row, cl).Value, adDouble
Next
End With
.Open
For rw = Source.Row + 1 To Source.Row +
Source.Rows.Count - 1
.AddNew
For cl = Source.Column To (Source.Columns.Count +
Source.Column - 1)
.Fields(cl - Source.Column) = Cells(rw, cl)
Next
.Update
Next
.MoveFirst

End With


End Sub
Sub Show_RST()
Dim wb As Workbook
Dim cl As Long

Set wb = Workbooks.Add

With wb.ActiveSheet
For cl = 1 To rst.Fields.Count
.Cells(1, cl) = rst.Fields(cl - 1).Name
Next
.Range("A2").CopyFromRecordset rst
End With

End Sub


Run the procedure 'main' will first build the recordset,
then will place the data to another workbook so you can
see that the build worked.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi

How do I create a recordset from a range (A1:G100) or
named range (Adress,
referes to A1:G100)?

I´ve tried

rs.open("SELECT * FROM Adress")

but what is the ActiveConnection?

Regards
PO


.