Lookup against External Database
At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells. I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.
While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.
To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.
The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).
For this function to work, a reference to "Microsoft ActiveX Data Objects x.x Library " must be made in VBA (under Tools Menu/References).
The code for the function is as follows:
Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As
String, FldName As String, ConnStr As String)
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
SQLookup = "Error"
On Error GoTo Exit_Func
SQLookup = "No Conn"
Set RecSet = New Recordset
SQLookup = "Error"
RecSet.Open "SELECT " & FldName & " FROM " & TblName & " where " & LkUpFld & " like '" & LkUpValue & "'", ConnStr, , , adCmdText
SQLookup = RecSet.Fields(FldName)
Set RecSet = Nothing
Set Conn = Nothing
An example of the connection string to this database would be as follows:
"DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits"A good reference for conneciton strings is this site.
To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP("Grapes","Fruit","tblFRUIT","Quantity",A1). The cell A1 contains the connection string.
This function looks for the field "Grapes" under the field "Fruit" from the database table and returns the corresponding value under the field "Quantity", which is 31.
Like all Excel functions, the various parameters can come from other cells, see example below.
Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).
A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.