How to connect Excel to MS SQL and get data WITH column names?

One of my users wants to get data into Excel from SQL 2008 query/stored proc.

I never actually did it before.

I tried a sample using ADO and got data but user reasonably asked - where are the 开发者_开发百科column names?

How do I connect a spreadsheet to an SQL resultset and get it with column names?

Apparently the field names are in the recordset object already.. just needed to pull them out.

i = 1
For Each objField In rs.Fields
    Sheet1.Cells(1, i) = objField.Name
    i = i + 1
Next objField

I don't know which version of Excel you are using but in Excel 2007 you can just connect to the SQL DB by going to Data -> From Other Sources -> From SQL Server. After you select your server and database, your connection will be created. Then you can edit it (Data -> Connections -> Properties) where in the Definition tab you change the Command type to SQL and enter your query in the Command text box. You can also create a view on the server and just point to that from Excel.

This should do it unless I misunderstood your question.