I have a script (below) which selects an array from a database and copies the values into excel okay. However, I want to transpose the table from this:

FY year Revenues
FY0 2013 11,019
FY1 2014 11,329
FY2 2015 11,787
FY3 2016 12,359

to This:

FY FY0 FY1 FY2 FY3
year 2013 2014 2015 2016
Revenues 11,019 11,329 11,787 12,359

I have tried playing around with the "Application.Transpose" function and also by calling a transpose function (shown), but with no success. Would someone be able to advise me of the correct syntax to use in the below example to transpose the recordset?

thanks


Set conn = New ADODB.Connection

conn.Open "Driver={MySQL ODBC 5.2a Driver};Server=" & Server_name & ";Database=" & Database_name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
   
    sql = "" & Field_selection & ";"
    Set rs = New ADODB.Recordset
    rs.Open sql, conn, adOpenStatic
                
                
    Worksheets(Output_sheet_name).Range(Output_cell_address).CopyFromRecordset rs
                
   For intFieldIndex = 0 To rs.Fields.Count - 1
      Worksheets(Output_sheet_name).Cells(Range(Output_cell_address).Row - 1, intFieldIndex + Range(Output_cell_address).Column).Value = rs.Fields(intFieldIndex).Name
   Next intFieldIndex
        
  ' Call function to Transpose
   TransposeDim (recArray)

    rs.Close
    conn.Close
   Set rs = Nothing
   Set conn = Nothing
    
End Sub


Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose array (v)
    
    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant
    
    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)
    
    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X
    
    TransposeDim = tempArray


End Function