CODEWITHSUNDEEP
vbaexcelloopsrecordset
Kevin Liss
Kevin Liss

Reputation: 136

VBA & Excel - Loop eecordset

I need to loop an recordset in VBA to fill my Excel sheet. I want to do this for each row.

What I've got now is:

comm.CommandText = str_SQL
rec.Open comm
Sheets("mySheet").Range("A4").CopyFromRecordset rec

This works. But I want to change the color of the cell when the index change in my rec is X.

Is this possbile? Has someone got another solution?

Upvotes: 0

Views: 63

Answers (1)

Martin Dreher
Martin Dreher

Reputation: 1564

So far you output all of the recordsets contents at once, which is way faster than looping every field in every record (at least for large recordsets).

If you wanted to actually loop, it would would something like this (messy and untested):

    Dim i As Integer, j as Integer
    Dim fld As Object

    ' print headers, starting from A4 to the left
    i = 1
    j = 4
    For Each fld In .Fields
        ThisWorkbook.Sheets(mySheet).Cells(j, i).Value = fld.Name
        i = i + 1
    Next fld

    ' print record by record, field by field
    i = 1
    j = j + 1
    While Not .EOF
        For Each fld In .Fields
            ThisWorkbook.Sheets(mySheet).Cells(j, i).Value = fld.Value
            ' here you could test for something like
            ' If fld.Name = "change" and fld.Value = "X" Then ThisWorkbook.Sheets(mySheet).Cells(j, i).Interior.Color = vbYellow
            i = i + 1
        Next fld
        j = j + 1
        .MoveNext
    Wend

This can be streamlined quite some more, but generally, you would have to use two nested loops for the records, and for the fields in each record. Which might be too clumsy for what you try to do.

A more reasonable approach would be to use something simple like

Dim cell As Object
For Each cell In Sheets("mySheet").UsedRange '<- replaced UsedRange with the column where "change" is printed, if there's the possibility that "X" is in other columns aswell
    With cell
        If .Value = "X" Then .Interior.Color = vbYellow
    End With
Next cell

to just format your cells, independent of your already processed recordset, or add conditional formatting to said range (with vba), which has the advantage of updating itself

Upvotes: 2

Related Questions