Connect and Pull data from SQL Server database in Excel using VBA

You can use Excel as a database, as long as your requirements are limited. Create multiple sheets in your workbook, store various type data in the sheets and work with the data as and when required. However, sometimes this is not enough and you’ll need an RDBMS like SQL Server to store data. Here in this post, I’ll show how to connect and extract or pull data from an SQL Server database in Excel using VBA.

Connect and Pull data from SQL Server in Excel using VBA

The image above clearly explains what my macro here will do. I’ll first fill a combo box with few records on button click. Next, when I select a value from the combo box, it will show more details in the following rows.

Create a Table in SQL Server

You’ll need SQL Server (any version) installed in your computer to work with the examples that I am sharing here.

Next, create a table named dbo.Birds and add few rows in it.

The Macro

I’ll add a few ActiveX controls like a button and combo box on my sheet. It is like a form.

Add a Reference of ActiveX Data Object

Before you write any code, you will need to add a Reference of ActiveX Data Object or ADO. This will provide the necessary classes, properties and methods to connect database objects.

To add reference to your project, click the Tools menu on the top and choose References… option. In the references box, find Microsoft ActiveX Data Objects 6.1 Library (or any current version). Click OK.

Add a Module

From the Project Explorer, right click the project and insert a module.

Add a Module in Excel VBA

The Module has a procedure to connect to the database using ADO classes and properties. It also has few Public variables.

Option Explicit

Public myConn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public sQuery As String

' Set the connection.
Sub setConn()
    If myConn.State = adStateOpen Then
        myConn.Close
    End If

    ' Define the connection string by provider driver and database details.
    Dim sConnString As String
    sConnString = "Driver={SQL Server};Server=arun;Database=your_database_name;Uid=sa;pwd=any_password;Connect Timeout=500;"

    myConn.ConnectionString = sConnString
    myConn.Open         ' Now, open the connection.
End Sub

See the connection string, where I have defined the Driver and other SQL Server properties like the user name and password.

Procedures to Extract or Pull data from SQL Server

Now, from the Project Explorer, double click the Sheet1 and write these codes.

Option Explicit

Private Sub cmdFillCombo_Click()
    setConn     ' Set the connection first.

    Sheet1.cmbBirdType.Clear        ' Clear the combo box.
    
    sQuery = "SELECT DISTINCT TypeOfBird from dbo.Birds"
       
    If rs.State = adStateOpen Then
        rs.Close
    End If
   
    ' Execute query using recordset object.
    rs.CursorLocation = adUseClient
    rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
    
    ' Fill combo box with records.
    If rs.RecordCount > 0 Then
        Do While Not rs.EOF
            Sheet1.cmbBirdType.AddItem rs.Fields(0).Value
            rs.MoveNext
        Loop
    Else
        MsgBox "No data found.", vbCritical + vbOKOnly
        Exit Sub
    End If
End Sub

' Show other details on combo box change event.
Private Sub cmbBirdType_Change()
    If Trim(cmbBirdType.Text) <> "" Then
        
        setConn     ' Set connection to the database.
    
        ' SQL query to fetch details about Birds for the selected Bird type.
        sQuery = "SELECT * FROM dbo.Birds WHERE " & _
            "TypeOfBird = '" & cmbBirdType.Text & "' " & _
            "ORDER BY BirdName"
        
        If rs.State = adStateOpen Then
            rs.Close
        End If
        
        ' Execute query using recordset object.
        rs.CursorLocation = adUseClient
        rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
        
        Dim iCnt As Integer
        iCnt = 11
        
        Sheet1.Range("A11:B20").Clear
        ' OR, you can use..
        ' Worksheets("sheet1").Range("A11:B20").Clear
        
        ' Finally, show the details.
        If rs.RecordCount > 0 Then
            Do While Not rs.EOF
                Cells(iCnt, 1) = rs.Fields("BirdName").Value
                Cells(iCnt, 2) = rs.Fields("ScientificName").Value
                
                rs.MoveNext
                iCnt = iCnt + 1
            Loop
        End If
    End If
End Sub

The first procedure or event is the click event of an ActiveX button control. It will fill the combo box with distinct type of Birds, extracted from the Birds table.

The second event is the combo box change event Private Sub cmbBirdType_Change(). When you select a value from the combo box, it will trigger the change event, which will again connect SQL Server to fetch related data from the table and show the data in a tabular format.

The macro above just shows how you can connect to an SQL Server database and pull or fetch data from a table. Once the connection is made properly, you can execute other queries, like insert and update to manipulate data in the table.

← PreviousNext →