ansaurus

Question

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

Answer 1

A:

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

Bobb 2010-08-16 17:00:51

Answer 2

A:

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.

kaloyan 2010-08-16 20:27:45

I use 2003.. However - in your answer on 2007 - will you get column names with the resultset from SQL?? thats the question (not how to get data - thats simple, but how to get it with column names - see bold font in the question).. Cheers

Bobb 2010-08-18 17:24:21

you would get the column names, yes.

kaloyan 2010-08-19 06:19:31

thank you. will keep in mind if we move to 2007.

Bobb 2010-08-19 08:34:42

I bet this feature is in 2003 as well -- I just don't have it in front of me to tell you what the exact interface steps are. I am sure that if you click around you will find it, though. There is a benefit in using the built-in Excel features and not VBA.

kaloyan 2010-08-19 15:51:22

ansaurus

tags:

views:

answers:

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

related questions