CopyFromRecordset does not keep sorting of recordset (1 Viewer)

[ino_mart]

All

I have sub below which exports an ADODB-recordset to Excel. Before the export, the recordset is sorted. In the loop I print out the supplier name in the Immediate window where the values are indeed sorted upon supplier name.

However, in Excel the records are in the original order instead of "supplier name". How can I fix this? I found a solution for DAO-recordsets where the original recordset (rs) is put into another DAO-recordset (set rs2=rs.openrecordset) but in ADO this "openrecordset" does not exist.

Private Sub ExportExcel(rs As ADODB.Recordset)
Dim app As Object
Dim wkb As Object

Set app = CreateObject("Excel.application")
Set wkb = app.workbooks.Add
app.Visible = True
rs.MoveFirst
rs.Sort = "[supplier name]"
While Not rs.EOF
    Debug.Print rs.Fields("supplier name")
    rs.MoveNext
Wend

app.range("A2").copyfromrecordset rs
End Sub

 

[arnelgp]

you can Clone the recordset in ADO. you can also Requery it.

 

[Minty]

If your recordset is based on a query simply sort the query itself before creating the recordset?

 

[ino_mart]

Cloning the recordset gives the same result: the output is in the original order, not the sorted order
Requering the recordset results in an error "operation is not allowed in this context"
The recordset is not based upon a query.

In meantime I found some solution, maybe not the best, but it works

Private Sub ExportExcel(rs As ADODB.Recordset)
Dim app As Object
Dim sht As Object
Dim wkb As Object
Dim rsExport As ADODB.Recordset
Dim i As Long

Set app = CreateObject("Excel.application")
Set wkb = app.workbooks.Add
app.Visible = True
rs.MoveFirst
rs.Sort = "[supplier name]"
CreateTable rsExport     'This sub creates the in-memory table and fields in exactly the same way as "rs" is created

Do While Not rs.EOF
    rsExport.AddNew
    For i = 0 To rs.Fields.Count - 1
        rsExport.Fields(rsExport.Fields(i).Name) = rs.Fields(i).Value
    Next i
    rsExport.Update
    rs.MoveNext
Loop

app.Range("A2").copyfromrecordset rsExport
End Sub

 

[Isaac]

You don't need to be creating a table. As Minty said, just sort the original recordset when opening it. Rather than deliberately creating an unsorted one, then trying to sort it after opening.