Export Access VBA Recordset To Single Row In Excel

Question

I am trying to write some VBA to export a recordset from Access to Excel. I have finally come down to the below export process, but it exports to columns A and B, and I want all of the data to be exported to row 2.

What would be the proper protocol/syntax to export to row 2?

Do While Not rsone.EOF
   columnNum = 1
   rowNum = rowNum + 1
   For Each fieldsss In rsone.Fields
       newExcelApp.Cells(rowNum, columnNum) = fieldsss.Value
     columnNum = columnNum + 1
   Next fieldsss
   rsone.MoveNext
Loop

score 3 · Accepted Answer · answered Nov 14 '17 at 08:48

3

kurtz's answer was nearly right, but he forgot to stop resetting the column number, making each next record overwrite the previous one.

Use this:

columnNum = 1
Do While Not rsone.EOF
   For Each fieldsss In rsone.Fields
       newExcelApp.Cells(2, columnNum).Value = fieldsss
     columnNum = columnNum + 1
   Next fieldsss
   rsone.MoveNext
Loop

answered Nov 14 '17 at 08:48

Erik A

31,639
12
42
67

That's what happens when you're falling asleep while typing :) – hod Nov 14 '17 at 19:11

Export Access VBA Recordset To Single Row In Excel

1 Answers1