What is the best way to push RecordSets onto the spreadsheet

[deadseasquirrels]

I have a macro that hits an Oracle Database. I use ADO, and I've found this to be the best way to manage the data. However I can't figure out a good way to import the results of the query back to the spreadsheet. I can do a loop that goes through all the fields and builds it row by row, but I still need some intelligence to move the cursor to the next row after each entry. Like i said it can be done, but I am looking for a more elegant way that can just take the recordset put it in a while loop before EOF, and just say put the recordsets info into row x, and increment that row x. I am not sure if that is even a functionality Recordsets allow for, but if it is can somebody please help. My code is below.

Private Sub emailBlast()

    Dim dbsNDF As New ADODB.connection
    Dim connString As String
    Dim dbCommand As ADODB.Command
    Dim dbRS As Object
    Dim sqlString As String
    Dim numFields
    Dim recordSetArray As Object
    Dim i As Integer
    
    Set dbsNDF = New ADODB.connection
    Set dbCommand = New ADODB.Command
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[uid];Data Source=[database];Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open
                
    sqlString = "[some query]"
                        
    dbCommand.ActiveConnection = dbsNDF
    dbCommand.CommandText = sqlString
    
    Set dbRS = dbCommand.Execute()
    numFields = dbRS.Fields.Count()
    

    Do Until dbRS.EOF
        For i = 0 To numFields - 1
            MsgBox dbRS.fields(i)   'This is a cheesy way
        Next i
        MsgBox vbCr
        dbRS.MoveNext
    Loop
    

CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
End Sub

 

[dk]

Hi,

As long as you're using Excel 2000 or greater then you can use the CopyFromRecordset method to copy an ADO recordset directly to a specified range. It's very quick.

HTH
Dan