Excel Database Query Add-in

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!

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

Public Function AccountName(AccountNum As String, Optional DSN As String = "DSNNAME") As String
'
' Function to read accountname from data file
'
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim myResult
Set oConn = CreateObject("ADODB.Connection")
' Tailor the drivername to suit
oConn.Open "Driver:={Drivername};DSN=" & DSN & ";"
Set oRS = CreateObject("ADODB.Recordset")
With oRS
    .CursorLocation = adUseClient
    .Cursortype = adOpenStatic
End With

' Tailor this to your software
sSQL = "Select Accounts.AccountName FROM Accounts " & "WHERE (Accounts.AccountNumber = '" & AccountNum & "')"
oRS.Open sSQL, oConn


myResult = oRS.GetRows()
AccountName = myResult(0, 0)

oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Function

Thank you.