How to write an Excel function which returns a value from an SQL database?

Question

I want to write the following function which should be used in an Excel worksheet:

=GetRecField("Foo Record Key", "FooField1")

...which will connect internally through ODBC to an SQL database, execute there an

SELECT FooField1 FROM MyTable WHERE KEY_FIELD='Foo Record Key';

and will return the resulting value as the result of the function GetRecField. The above SQL is granted to return only one record (IOW KEY_FIELD has an unique constraint).

Of course, the above function can be called multiple times in a worksheet so, please try to avoid a blind QueryTables.Add

TIA.

Answer 1

You can write a custom function to do that

1. Open the VBA editor (ALT-F11)
2. Open Tools -> References, and make sure the "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ActiveX Data Objects Recordset 2.8 Library" are selected
3. Right click VBAProject, and choose Insert -> Module
4. Open the module. Now you can create a custom function, like:

    Public Function GetItem(field As String, id As Integer) As String
        Set oConnection = New ADODB.Connection
        Dim oRecordset As ADOR.Recordset
        oConnection.Open "provider=sqloledb;data source=yourserver;" & _
            "Trusted_Connection=yes;initial catalog=yourdatabase;"
        Set oRecordset = oConnection.Execute( & _
            "select " & field & " from table where id = " & id)
        If oRecordset.EOF Then
            GetItem = "n/a"
        Else
            GetItem = oRecordset(field)
        End If
    End Function

5. You can now call the function from a cell:

   =GetItem("fieldname";2)

The module is required because non-module functions can't be called from inside the spreadhseet.