SQL Server Database Integration with EXCEL

Hi,

I wanted help with regards to writing a VBA code that would include an SQL query to pull data directly from SQL server on to my spreadsheet. However due to my limited knowledge of VBA I am having difficulty designing the connection string as well as the overall structure.

SQL Server = SNLDB2
Windows authenticated settings ( no login required)

Any help regarding this would be greatly appreciated. Please do let me know if any other piece of info. is required from my side. Thanks

try this
you have to reference the "Microsoft ActiveX Data Objects 2.X Library" in tools --> reference

Code:

---

Dim qry_my as String,conn_str as String
Dim db_my As ADODB.Connection
Dim rs_my As ADODB.Recordset
conn_str="Provider=SQLNCLI;Server=Your Server name;Database=Your databasename;Uid=yourusername;Pwd=yourpassword;"
qry_my = "Your query here"
Set db_my = New ADODB.Connection
Set rs_my = New ADODB.Recordset
db_my.open conn_str
rs_my.Open qry_my, db_my, adOpenStatic, adLockReadOnly
range("a1").CopyFromRecordset rs_my
rs_my.close
set rs_my = Nothing
db_my.close
set db_my = Nothing

---

for more connection strings see here http://www.connectionstrings.com/

Thanks Mohan , I will try this and report back