Transposing CopyFromRecordset Excel VBA

Question

I have the following code in my Excel VBA that copies data from a table in SQL into Excel. This data is being inserted horizontally starting on cell C2, but I want it to be inserted vertically on column C.

Sheets("Control").Range("C2").CopyFromRecorset rsPubs

Where rsPubs is my ADO connection.

Basically, I just want this data transposed. What's an efficient way of doing this?

This is how rsPubs is created (the connection works fine as I'm actually getting the data):

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT * FROM Analytics.dbo.XBodoffFinalAllocation"
    ' Copy the records into cell B3 on Sheet1.
    Sheets("Control").Range("C2").CopyFromRecordset rsPubs
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

Answer 1

I cannot test this currently, but you could:

Sheets("Control").Range("C2").CopyFromRecorset rsPubs 'copy your data
Sheets("Control").Range("C2").Copy 'copy the data into clipboard
Sheets("Control").Range("C2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, True, True

Also you could use the Transpose Worksheetfunction - however, I don't quite see a way right now to do this directly, expect your input data is transposed already.

Here is a nice official example and further informations on this topic: How to transfer data from an ADO Recordset to Excel with automation

Especially the "using GetRows" section.

This should do:

Dim resultset As Variant
Dim result As Variant
resultset = rsPubs.GetRows
result = Application.WorksheetFunction.Transpose(resultset)
Sheets("Control").Range("C2").Resize(UBound(result, 1), UBound(result, 2)) = result

http://www.teachexcel.com/excel-help/excel-how-to.php?i=147811

Answer 2

Edit 2 in the accepted answer doesn't work for me, but the following does (see http://www.mrexcel.com/forum/excel-questions/513845-copyfromrecordset-transpose.html for my source):

Public Sub PlaceTransposedResults(oResults As ADODB.Recordset, rTarget As Range)
Dim vTransposed As Variant

If Not oResults.EOF Then
    vTransposed = oResults.GetRows
    rTarget.Resize(UBound(vTransposed, 1) + 1, UBound(vTransposed, 2) + 1) = vTransposed
End If
End Sub

(this assummes that you haven't changed the array base with the OPTION BASE and that your version of Excel has Range.Resize and that oResults is never nothing)

One tweak on this is to make this a function and return the correctly sized range - useful if you want to resize a named range to cover the result set.

Another likely tweak is that you may want to optionally allow the user to ask for the field names to be added as the in the first column. I have found nothing better than:

Dim ix As Integer
For ix = 0 To oResults.Fields.Count - 1
    rTarget.Offset(ix, 0) = oResults.Fields(ix).Name
Next ix

(of course you then have to offset your main results by 1 column in this case).