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

Question

Hi all,

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

Why not use Excel's built-in database query functionality on a separate sheet (Data menu, Import External Data, New Database Query), then use VLOOKUP to extract values from that sheet?