Use Database functions

Use Database functions

DBFuncs is an Excel-addin for database querying by userdefined functions. This is opposed to the Excel integrated Microsoft-Query, which is integrated statically into the worksheet and has some limitations in terms of querying possibilities and flexibility of constructing parameterized queries (Microsoft-Query allows parameterized queries only in simple queries that can be displayed graphically).

Furthermore it includes the possibility for easy filling of so called “data bound controls” (ComboBoxes and Listboxes) with data from queries.

DBFuncs has been tested extensively (actually it’s in production) only with Excel XP and Microsoft-SQL Server, other databases (MySQL, Oracle, PostgreSQL, DB2 and Sybase SQL Server) have just been tested with the associated Testworkbook DBFuncsTest.xls.

To use that Testworkbook you’ll need the pubs database, where I have scripts available for Oracle, Sybase, DB2, PostgreSQL and MySql on my website (the Microsoft-SQL Server version can be downloaded here).

There are two principal ways to query data with DBFuncs:

1. A (fast) list-oriented way using DBListFetch.
   Here the values are entered into a rectangular list starting from the TargetRange cell (similar to Microsoft-Query, actually the QueryTables Object is used to fill the data into the Worksheet).
2. A record-oriented way using DBRowFetch.
   Here the values are entered into several ranges given in the Parameter list TargetArray. Each of these ranges is filled in order of appearance with the results of the query.

Additionally, some helper functions are available:

• chainCells, which concatenates the values in the given range together by using “,” as separator, thus making the creation of the select field clause easier
• concatCells simply concatenating cells (making the “&” operator obsolete)
• DBString, building a quoted string from an open ended parameter list given in the argument. This can also be used to easily build wildcards into the String
• DBinClause, building an SQL in clause from an open ended parameter list given in the argument
• DBDate, building a quoted Date string (format YYYYMMDD) from the date value given in the argument
• MarktwertHolen, fetching Date-oriented market data into a single cell