Recordset in Excel

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

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

I assume that a row will constitute a record?
 
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?
 
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
 
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.

--
 
Back
Top