Loop through recordset and write records to specific rows based on condition

I have a recordset looping question for which I did not see a solution here that I can adapt, so I ask this as a question:

I have an Excel template for a list of line items, item 1 to 20, which are listed in rows 1 to 20 of the template. The cells A1 to A20 have the running number values "1" to "20" and this never ever changes.

I have an SQL Server database table that contains all line items of all commissioners. The table contains the commissioner name and the running numbers, among other things.

Initially, if 10 items are commissioned, they live in lines 1 - 10 of the template, and have the corresponding running number in the database. What happens then is that some of these 10 items are sold, are taken off the list, and get deleted or archived in the database.

So I end up with a recordset with running numbers 1, 3, 4 and 7, as an example. What I need is that when I populate the Excel list with the remaining records from the database, that the remaining running numbers to sit in "their" row, where cell value in row A matches with running number of record.

The code I have loops though the recordset and just slaps the 4 remaining records into lines 1 to 4.

But what I need is a loop that moves from one line of the template to the next, checks the cell value in column A, checks if there is a corresponding running number value in the recordset, puts the record in that line if true, and moves to the next line.

Maybe I have to make the value of the "row" variable dependent on which running number values I have in my recordset, but that feels awfully complex, so maybe someone with more experience has a simpler solution.

My code (which is a simple "slap everything in" loop) looks like this:

The commissioner is selected in the SQL Query, and the "row" variable is defined elsewhere and just states the row number to start with.

Sub test()
    Do While (Not rs1.EOF)

        'I tried at different places (inside and outside of the Do...Loop)
        'If rs1!RunningNumber.Value = ActiveSheet.Range("A" & Row).Value Then
        'but that only had the effect of stopping the loop afetr the first record.
        ActiveSheet.Range("B" & Row).Value = rs1!ArticleDescription.Value
        ActiveSheet.Range("F" & Row).Value = rs1!Size.Value
        ActiveSheet.Range("G" & Row).Value = rs1!CategoryID.Value
        ActiveSheet.Range("I" & Row).Value = rs1!Price.Value
        ActiveSheet.Range("L" & Row).Value = rs1!AdditionalInfo.Value
        Row = Row + 1
End Sub

1 answer

  • answered 2021-05-05 12:08 Nicholas Hunter

    A couple of general principles. (1) Try to avoid using Select in your VBA code whenever you can. Some examples of how to avoid select. (2) Move everything that does not depend on the value of the iterator out of the loop.

    From your description, it sounds like you should just set the value of the worksheet target row to the value of the "running number" in the database table. Give this a try an see if it works for you.

    Sub test()
        Dim ws as worksheet
        Set ws = Worksheets("Tabelle1")
        Do Until rs1.EOF
            Row = rs1!RunningNumber.Value
            ws.Range("B" & Row).Value = rs1!ArticleDescription.Value
            ws.Range("F" & Row).Value = rs1!Size.Value
            ws.Range("G" & Row).Value = rs1!CategoryID.Value
            ws.Range("I" & Row).Value = rs1!Price.Value
            ws.Range("L" & Row).Value = rs1!AdditionalInfo.Value
    End Sub