Pulling Column Names into Excel from SQL query

Question

I'm using Excel to pull data from an SQL db. I used the code from another SO question and it works fine. Now I want to pull in the column names from a table in addition to the actual table. I figured out that I could get the names using the For Each fld loop. However there's still the issue of populating them horizontally in a row in Excel as the number of columns might change - so I'm thinking I would need another For each loop also or something similar.

Sub GetDataFromADO()

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
    objMyConn.Open

'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = "select * from myTable"
    objMyCmd.CommandType = adCmdText
    objMyCmd.Execute

'Loop Names'
    ' WHAT TO DO HERE????'

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open objMyCmd

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

Don't you want the headers lined up with the data? The data starts in A1, but you seem to have set the header range to A4. — Lance Roberts, Nov 16 '10 at 01:52

score 24 · Answer 1 · answered Nov 18 '10 at 14:59

24

My usual code is very similar:

For intColIndex = 0 To objMyRecordset.Fields.Count - 1 
    Range("A4").Offset(0, intColIndex).Value = objMyRecordset.Fields(intColIndex).Name
Next

answered Nov 18 '10 at 14:59

dendarii

2,958
20
15

I like the "shortness" of your solution. I just expanded it a bit to set the headers to Bold. – Vincent Vancalbergh Mar 03 '11 at 13:23
2

I am baffled at the absence of a solution which does not resort to a loop. – mvbentes May 06 '16 at 16:50

score 13 · Accepted Answer · edited May 14 '15 at 17:15

13

Ok so I figured it out after 4 attempts, here's the code for the loop.

 'Loop'
 Dim FieldRange As Range
 Set FieldRange = Range("A4")
 Set TableColumns = Range("A4:H4")
 x = 1

 Range("A4").Select

 For Each fld in objMyRecordset.Fields
      ActiveCell.Value = fld.Name
      ActiveCell.Offset(0, x).Select
      x = x + 1 'tick iterator
 Next

 ActiveSheet.Range("A5").CopyFromRecordset objMyRecordset
 Range("A4").Select

edited May 14 '15 at 17:15

Chris Walbrun

3
2

answered Nov 16 '10 at 05:14

firedrawndagger

3,573
10
34
51

2

You don't need to select the Cell use something like `Range("A4").OffSet(0, x).value = fld.Name` – Rafa Barragan May 02 '16 at 18:01

score 8 · Answer 3 · answered Jul 29 '14 at 11:35

8

To make it super simple, do something like this (using Sheet1 and recordset r)

    For i = 0 To r.Fields.Count - 1
        Sheet1.Cells(1, i + 1) = r.Fields(i).Name
    Next i

answered Jul 29 '14 at 11:35

protoculture

141
2
2

score 3 · Answer 4 · answered Mar 17 '17 at 18:49

3

You can just set your "x" variable to 0 and then do something like:

x = 0

For Each Field In RS.Fields 'RS being my Recordset variable
    Range("A3").Offset(0, x).Value = Field.Name
    x = x + 1
Next Field

And that will make it a bit easier to read... :)

answered Mar 17 '17 at 18:49

Tony Diaz

41
1

Isn't this answer DAO instead of ADO? – John Shaw May 10 '19 at 18:48

Pulling Column Names into Excel from SQL query

4 Answers4

Linked