Recordset in Excel

[Newbie]

Hi,

How do I create a recordset based on a range of cells?

I assume that a row will constitute a record?

 

[Tom Ogilvy]

Define what you mean by RecordSet. Are you using ADO, DAO, what?

Is the Excel file open or closed? Where are you doing this from?

 

[Mike]

First you have to reference the DAO or ADO library.
Current best approach is to use ADO 2.7 (if that's still
the latest version). Then I think all you can do is loop
through the range of cells and append values from
selected columns into the recordset. For example:

Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long

Set rs = New ADODB.Recordset
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

rs.Fields.Append "Field1", adBSTR
rs.Fields.Append "Field2", adBSTR
rs.Open

For i = 1 To 50
rs.AddNew
rs("Field1").Value = ws.Cells(i, 1).Value
rs("Field2").Value = ws.Cells(i, 2).Value
rs.Update
Next

 

[onedaywhen]

Mike's is an example of a fabricated recordset. I'll point out the
obvious: you can also use ADO to connect to the workbook using the MS
OLEBD provider for JET and create an *updateable* recordset based on a
SQL SELECT query.

--