CODEWITHSUNDEEP
ms-accessvbams-access-2013
BellHopByDayAmetuerCoderByNigh
BellHopByDayAmetuerCoderByNigh

Reputation: 1167

Recordset Not Iterating

I am sure this is strictly user error, but for the life of me, I can not discover how to iterate a table, and write the records to Excel. I have the below code, but it hangs on the first Manager ID and just repeats the write for that one constantly in a loop. I want to iterate all the Manager ID in the table and write them to the same workbook.

How should I tweak this code in order to do such?

  Set xlR = xlWb.Worksheets(1).Range("$R$2")
  i=0
  Set rs2 = Db.OpenRecordset("SELECT * FROM TestTable ORDER BY [Manager ID] ASC", dbOpenDynaset)
  managerName = CLng(rs2.Fields(3).Value)
  Debug.Print managerName
  With rs2
     .MoveLast
     .MoveFirst
     Do While Not .EOF
        xlR.Value = .Fields(0).Value
        xlR.Offset(ColumnOffset:=1 + (i * 2)).Value = .Fields(2).Value
        xlR.Offset(ColumnOffset:=2 + (i * 2)).Value = "ENTATH01"
        i = i + 1
        .MoveNext
     Loop
     .Close
  End With
  xlWb.SaveAs FileName:=sPath & sFile, FileFormat:=xlOpenXMLWorkbook
  xlWb.Close SaveChanges:=True
  rs2.MoveNext

Upvotes: 0

Views: 65

Answers (2)

Erik A
Erik A

Reputation: 32672

You're not changing your offset, so you're continuously assigning the same cell.

Try the following:

    xlR.Offset(ColumnOffset:=i * 3).Value
    xlR.Offset(ColumnOffset:=1+(i*3)).Value = .Fields(2).Value
    xlR.Offset(ColumnOffset:=2+(i*3)).Value = "ENTATH01"

I assume you're initializing i to 0

Upvotes: 3

user2261597
user2261597

Reputation:

Change:¨

i = i + 1
.MoveNext

to 

i = i + 2
.MoveNext

Upvotes: 0

Related Questions