How to connect Excel to an RDBMS Like SQL Server

yslguru

New Member
Joined
Oct 27, 2016
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Is it possible in Excel to connect to a SQL Server DB Instance to pull data via a query?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yes. Something like this..
VBA Code:
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
 
Upvote 0
Also,
1714199489330.png
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top