Recordset in Excel

N

Newbie

Hi,

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

I assume that a row will constitute a record?
 
T

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?
 
M

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
 
O

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.

--
 
Top