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).

Answer 3

Untested:

Sub CopyTransposed(rng As Range, rs As ADODB.Recordset)
    Dim x As Long, y As Long
    x = 0
    Do While Not rs.EOF
        For y = 0 To rs.Fields.Count - 1
            rng.Offset(y, x).Value = rs.Fields(y).Value
        Next y
        x = x + 1
        rs.MoveNext
    Loop
End Sub

Answer 4

This is really simple. It uses Excel's built-in transpose. Can be transposed in a single statement. No looping is needed. Tested and validated.

Dim vRecs, vRecsX
vRecs = rsPubs.GetRows
vRecsX = Application.Transpose(vRecs)
Sheets("Control").Range("C2").Resize(rsPubs.RecordCount, rsPubs.Fields.Count) = vRecsX

This code is copied directly from a working example.

Note, since this is a worksheet function, expect it to work with arrays of size only up to the number of columns on a worksheet. I believe columns-per-sheet varies between different versions of Excel, so it's a safe bet to determine your transpose limit based on your version of Excel.

"In the modern versions of Excel this is column XFD, which is 16,384 columns. In older versions of Excel (2003 and prior) the last column was IV which is 256 columns." https://officemastery.com/_how-many-rows-and-columns-in-excel/

Answer 5

My suggestion is not to use VBA, at all. Microsoft already provided you the facility to import data from a database.

Data -> Import External Data

It will, then, create a QueryTable inside the sheet where you can just right-click and refresh in a regular basis. Another bonus is that you don't get the nasty macro warning. The QueryTable can be a table, query or stored procedure.

Try it!