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/