Excel Database Query Add-in

[pmcguire]

I am hoping to find an Excel add-in that functions like the product from F9 but can be configured to work against our system data. (Unfortunately F9 does not supply a version that works with our software.) The data is in a SQL database.

With the F9 software, you can display general ledger data in a spreadsheet cell using a formula like "=gl(account,type,year,month)" where "account" references a cell that contains, for example, "11100" or "11100..11199", "type" references a cell that contains "actual" or "budget", and so forth.

I hope to use this as a spreadsheet "front-end", replacing my software's report writers, which are terrible.

Your help is appreciated!

[Mikeopolo]

Some years ago I wrote a number of functions to read accounting data from my software in Excel. It was moderately useful. It doesn't profess to be best code practice, just what I could do at the time.

Below is an example; all the functions followed the same principle, with more complex Select statements in some cases. I found MS Query very useful in designing those, especially where date ranges are involved.

You can write your own function if you create a DSN to the database and modify the select statement below. This implies that you know the tables and relationships in your database.

This particular function would be called as a UDF, eg
=Accountname(A1,DSNname) - the parameters refer to two other cells

Please Login or Register  to view this content.

[pmcguire]

Thank you.