Adodb recordset object, columns and rows transposed

Hi All,

I'm using an ADODB connectino from excel 2007 vba to an oracle database to query data from teh database and return it to my workbook.

Everything is working excep the final step in which I take the rows from the adodb recordset object and put them into my workbook.

in this code is the adodb recordset

  Dim resultset As Variant
    resultset = sqlresults.GetRows
    
    .Range("A2").Resize(UBound(resultset, 1) - LBound(resultset, 1) + 1, UBound(resultset, 2) - LBound(resultset, 2) + 1) = resultset

It works, but it is transposing the columns and rows. So if I were looking at my query results in my oracle sql editor I'd be seeing rows but the same information is put into columns in the excel workbook by the vba.

Is it possible to get it to flip things back around without writing some kind of double nested loop thing to transpose the array?

Thanks,

Dave

Have you tried Transposing the recordset when applying to the Variant Array ?

resultset = Application.Transpose(sqlresults.GetRows)

Originally Posted by T-J

Use CopyFromRecordset

ws.Range("A2").CopyFromRecordset sqlresults

Perfect. That let me get rid of the transposing. Thank you!!!