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
– Lance Roberts, Commented Nov 16, 2010 at 1:52

dendarii · Accepted Answer · 2010-11-18 14:59:08Z

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, 2010 at 14:59

dendarii

3,10823 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vincent Vancalbergh Over a year ago

I like the "shortness" of your solution. I just expanded it a bit to set the headers to Bold.

mvbentes Over a year ago

I am baffled at the absence of a solution which does not resort to a loop.

Chris Walbrun · Accepted Answer · 2015-05-14 17:15:09Z

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, 2015 at 17:15

Chris Walbrun

32 bronze badges

answered Nov 16, 2010 at 5:14

firedrawndagger

3,76310 gold badges37 silver badges51 bronze badges

1 Comment

Rafa Barragan Over a year ago

You don't need to select the Cell use something like Range("A4").OffSet(0, x).value = fld.Name

protoculture · Accepted Answer · 2014-07-29 11:35:40Z

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, 2014 at 11:35

protoculture

1512 silver badges2 bronze badges

Comments

Tony Diaz · Accepted Answer · 2017-03-17 18:49:53Z

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, 2017 at 18:49

Tony Diaz

411 bronze badge

1 Comment

John Shaw Over a year ago

Isn't this answer DAO instead of ADO?

Collectives™ on Stack Overflow

Pulling Column Names into Excel from SQL query

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related