Using Excel VBA to Query a SQL Server Database

Although we can create External Data Sets in Excel that pull information from a SQL Server Database there are times when you want to check a value or lookup a value from an underlying SQL Server database directly from an Excel formula.

We will look at an example that enables us to lookup a customer’s total revenue value from an Excel Formula.

The VBA To Query a SQL Server Database

The following VBA Function can be used from within an Excel VBA Project. You will, however need to add the ADODB library to the project first.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConnString As String
 
    If intID = 0 Then
        LookupAWCustomerRevenue = 0
    Else
        strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _
                        & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"
        
        Set conn = New ADODB.Connection
        conn.Open strConnString

        Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)
        
        If Not IsNumeric(rs.Fields("CustRev").Value) Then
            LookupAWCustomerRevenue = 0
        Else
            LookupAWCustomerRevenue = rs.Fields("CustRev").Value
            rs.Close
        End If
    End If
    
End Function

In this example we use an ADO connection to connect to a SQL Server instance (DBSRV\SQL2014):

        strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _
                        & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• The Provider parameter indicates that an OLDEB connection will be established and the Data Source parameter points to the SQL Server Instance.
• The Initial Catalog parameter identifies the database to be queried (AdventureWorks2014)
• The Integrated Security parameter indicates that Windows Authemtication will be used to authenticate with SQL Server.

A RecordSet object (rs) is used to create a record set from a SELECT statement:

        Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

The SELECT statement is constructed from a literal string and the value for the variable intID that is passed into the function when it is called.

The If statement at the start checks for an intID value of 0. Integer variables default to a value of zero if not initialised (in other words if no value is provided when the function is called). If no value is passed in to the function a value of 0  is returned as the revenue value.

        If intID = 0 Then
               LookupAWCustomerRevenue = 0

The second If statement tests for a non numeric value being returned by the SELECT statement. If a customerID passed in to the function is valid, but they have plaved no orders the SUM(TotalDue) expression will return a NULL value. If this happens then the funvtion will return a value of 0 instead.

        If Not IsNumeric(rs.Fields("CustRev").Value) Then
            LookupAWCustomerRevenue = 0

The NULL value scenario can be seen in the following screen shot.

I placed a breakpoint on the line of VBA code containing the If statement and opened the Locals Window so that I could see all the variable values at that point of execution.

I tested the function by executing it from the Immediate Window in the VBA editor:

        ?LookupAWCustomerRevenue(1)

With a breakpoint set the code execution automatically stops at the marked line and enables us to view the environment at that point of execution.

The Locals Window in the above screen shot shows the Recordset object variable rs, and specifically the value for the first field from rs, “CustRev”. We can see that it is set to Null. This is because a Customer with CustomerID value 1 has not placed any orders and, therefore, has no resulting revenue value.

The following screnn shot shows that the query returns NULL when run directly on the SQL Server instance:

Assuming that a valid CustomerID is passed into the function and a non NULL value is returned by the SELECT statement, the function will return the total sales revenue for that customer as a currency value.

        LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Calling The VBA Function From An Excel Formula

Calling a VBA function from an Excel Formula is simple. When you construct an Excel Formula by typing it into a cell intellisense will display matching VBA functions as well as built in functions. The following screen shot shows this:

You can see that there are two VBA functions listed above that start with Lo: LookupAWCustomerRevenue and LookupPersonName.

The following example shows the LookupAWCustomerRevemue function being used in an cell to calvulate the total revenue for the CustomerID value sepcified in Column A of the worksheet:

Summary

In this article we have seen a bit of VBA coding, some VBA Error Handling and Debugging techniques, and we have seen how we can call VBA functions from an Excel formula. Feel free to email us at info@ptr.co.uk if you have any questions relating to this article. There is also an Access version of this article.

You might find that these courses come up on a Late Availability offer from time to time, offering savings of 30%.