getting data from SQL Server to Excel and vice versa

[flowergirl]

Hi,

I need to get data from MS SQL Server to Excel and vice versa.

Could you please send me the code for that.

Thanks for your help.

[mancubus]

hi.
google search:

xl -> sql
http://support.microsoft.com/kb/321686

sql -> xl
http://msdn.microsoft.com/en-us/library/cc952922(v=sql.100).aspx

[mohanvijay]

Try this (Using ADO reference)

refer this website more connection strings http://www.connectionstrings.com



Dim db_sl As New ADODB.Connection
Dim rs_sl As New ADODB.Recordset
Dim con_str As String, qry_sl As String
con_str = "Provider=SQLNCLI;Server=YourServerAddress;Database=YourDataBase;Uid=YourUse rname;Pwd=YourPassword;"
rs_sl = "SELECT * FROM tablename" 'Your Query
db_sl.Open con_str
rs_sl.Open qry_sl, db_sl, adOpenStatic, adLockReadOnly
Range("a1").CopyFromRecordset rs_sl 'Get data into Excel
rs_sl.Close
Set rs_sl = Nothing
db_sl.Close
Set db_sl = Nothing