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!
- It only returns the first value of the first row
- DLookup’s are not efficient opening a 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
3 responses