How to connect Excel to an RDBMS Like SQL Server

[yslguru]

Is it possible in Excel to connect to a SQL Server DB Instance to pull data via a query?

 

[Cubist]

Yes. Something like this..

Sub ConnectToSQLServer()
   Dim conn As Object
   Dim rs As Object
   Dim strSql As String
   Dim strConn As String

   strConn = "Provider=SQLOLEDB;Data Source=YourServerName;" & _
             "Initial Catalog=YourDatabaseName;" & _
             "User ID=YourUsername;Password=YourPassword;"
   strSql = "SELECT * FROM YourTableName;"
   Set conn = CreateObject("ADODB.Connection")
   conn.Open strConn
   Set rs = CreateObject("ADODB.Recordset")
   rs.Open strSql, conn

   If Not rs.EOF Then
       Do Until rs.EOF
           Debug.Print rs.Fields(0).Value
           rs.MoveNext
       Loop
   Else
       MsgBox "No records found."
   End If

   rs.Close
   conn.Close

   Set rs = Nothing
   Set conn = Nothing
End Sub

 

[Cubist]

Also,

 

[yslguru]

I'll check out both. Thanks!