Record Specific Rows

[JohnEOasis]

I am retrieving information from an SQL database and storing in a recordset. I want to get a specific column in the record set and insert in into an specific column in Excel. I can loop through the record set and see all the columns however I am not sure how to get to a specific column in the recordset. Here is how I currently access the recordset:

For Each Row In dbRows
recorfRange.offSet(nextR, 0) = Row
nextR = nextR + 1
Next

I would appriciate any help

 

[SkipVought]

Hi,

I can loop through the record set and see all the columns however I am not sure how to get to a specific column in the recordset.

dim fld as ADODB.Field

for each fld in YourRecordSet.Fields
   msgbox fld.name & ":" & fld.value
Next

or

  msgbox YourRecordSet("YourFiledName").value

assuming that you are looping thru the rows in your recordset.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NWBeaver]

Here is a piece of code that I have used to do something similar.

strName = "AreaID" 'Field name to look for.
col = 1
Row = 2 'Start with row 2, Field names are in row 1.
While Not rs.EOF
While col <= rs.Fields.Count
If rs(col - 1).Name = strName Then
ws.Cells(Row, col).Value = rs(col - 1).Value
Else
ws.Cells(Row, col).Value = "--"
End If
col = col + 1
Wend
Row = Row + 1
col = 1
rs.MoveNext
Wend

rs.Close
con.Close


It just loops through the record set and copies the values where the field name matches.
Note that the record set index starts at 0, while the Excel column numbers start at 1.

 

[JohnEOasis]

Thank you for your help. You all pointed me in the right direction here is what I came up with:

    While Not myRecordSet.EOF
        nameRange.offSet(nextR, 0) = myRecordSet(0).Value
       nextR = nextR + 1
    Wend

 

[JohnEOasis]

Code correction:

    While Not myRecordSet.EOF
       nameRange.offSet(nextR, 0) = myRecordSet(0).Value
       [b][COLOR=red]myRecordSet.Move (1)[/color][/b]
       nextR = nextR + 1    
     Wend