0

rather than have all my column names across the top of the worksheet, I'm looking to have them down the left hand side eg in Column A. The value of that will then be in Column B. I have used a record set which loops through and pulls the first column correctly, but I need 30 more columns to do the same with the values.

My code:

' Run the query and store the retrieved data in the recordset
Set adoRecordset = adoCommand.Execute
' Set to the first empty row
intRow = 10
' Loop through the recordset
Do While Not adoRecordset.EOF
    For rs = 0 To adoRecordset.Fields.Count - 1
    
        With shtData
                     
                With .Cells(intRow, intCol + 1)
               
                ' Write the data to the cells
                .Cells(intRow, intCol + 1).Value = adoRecordset.Fields(intCol).Name
                .Cells(intRow, intCol + 2).Value = adoRecordset.Fields(intCol).Value
          
                End With
           
        End With
    ' Go to the next column in the record
    Next rs
    'Next intRow
    
    ' Go to the next row
    intRow = intRow + 1
    ' Move to the next record in the recordset
    adoRecordset.MoveNext
Loop
' Reset the recordset
Set adoRecordset = Nothing
' Reset the command
Set adoCommand = Nothing
' Closet and reset the connection
adoConnection.Close
Set adoConnection = Nothing
ChrisR
  • 81
  • 9
  • ADO recordsets have a `GetRows()` method which will pull the data out into an array which is already oriented the way you want, so you can drop it directly on the worksheet. https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/getrows-method-ado?view=sql-server-ver16 – Tim Williams Dec 21 '22 at 16:56
  • It pulls it out and its fast but its in the wrong orientation. The column headers are along the top just like a db, but I'm looking for it down the row for the headers in excel column A. – ChrisR Dec 25 '22 at 00:04

0 Answers0