Connect To SQL Server Using VBA

1. Open Excel  -- Developer Tab -- Select Visual Basic -- Double click on Sheet1 (or require sheet)

Tools - Reference -  Microsoft ActiveX Data Objects 2.6 Library

Sub FetchFromSQLServer()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim constring As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
constring = "provider=SQLOLEDB.1;Data Source=SQLServer2005,1433;Initial Catalog=kalyandb;User id='user1';password='admin"
con.Open constring

rs.ActiveConnection = con
rs.Open "Select * from Colors"
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set con = Nothing

End Sub