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
