PDA

View Full Version : SQL Server Database Integration with EXCEL



Agha
10-18-2011, 06:53 AM
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

mohanvijay
10-18-2011, 08:05 PM
try this
you have to reference the "Microsoft ActiveX Data Objects 2.X Library" in tools --> reference



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/

Agha
10-18-2011, 08:11 PM
Thanks Mohan , I will try this and report back