DLookup for Excel

I had to do a couple of ad-hoc Excel jobs today and found that whilst Excel has a VLookup function for spreadsheet/ranges it doesn’t have one for databases.

It’s been a while since I touched VBA and Access but the DLookup function was quite useful so here it is for Excel. Read the warnings below before you use it!

Public Function DLookup(expression As String, domain As String, criteria As String, Optional connectionString As String)

    Dim con As ADODB.connection
    Set con = New ADODB.connection
    If IsMissing(connectionString) Then
        con.Open "Driver={SQL Server};Server=abc;Database=def;Trusted_Connection=Yes;"
    Else
        con.Open connectionString
    End If

    Dim rs As ADODB.Recordset
    Dim sql As String
    sql = "SELECT TOP 1 " + expression + " FROM " + domain + " WHERE " + criteria
    Set rs = con.Execute(sql)
    If Not rs.EOF Then DLookup = rs(0)
    rs.Close
    Set rs = Nothing

    con.Close
    Set con = Nothing

End Function

Copy and paste the code above into an Excel module and replace the Driver=... with what you want to be your default connection.

Some examples of use:

To select the Name column from the Customers table where the numeric ID is held in column 2.

=DLookup("Name","Customers","ID=" & A2)

To return Forename and Surname columns joined together with a space from the Contacts table in the crm database using the postcode from column 5:

=DLookup("Forname + ' ' + Surname","crm..Contact","PostCode='" & A5 & "'")

To return Description from a Products table held in another connection using a partial like match from column 1:

=DLookup("Description","Products","OrderCode LIKE '" & A1 & "%'", "DSN=SecondaryDSN")

I think you get the idea!

Caveats

There are a few caveats you need to be aware of using this function:

• It only returns the first value of the first row
• DLookup is not efficient as it opens connection each time it is referenced. This could put a lot of strain on your server.
• DLookup syntax is open-ended and has some attack vectors

If that doesn’t scare you then give it a shot. As usual no warranty expressed or implied!

[)amien