Search code examples
ms-accessvbams-access-2013recordset

Export Access VBA Recordset To Single Row In Excel

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
Solution

  • 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