External data sources in Excell...

G

George

Hi,

I have to write a few Excell functions manipulating on
external data sources which are standard DBF tables. The
functions should create an Excell library.
For example: the DBF table C:\My documents\Invoice.dbf
contains the field InvAmount. I would like to sum it for a
given range of records and the result put in an Excell
cell.
How to do it or where I could find something helpful.
Visual Basic for Application doesn't contain suitable
functions.

Thanks in advance,
George
 
D

Den

I have not worked with DBF tables before but I can tell you one way it is
done with Access tables (MDB) or SQL. In Access you would first build a
query that does the sum (or whatever) that you want. You can then use
Excel's Get external data wizard to pull the data from that query.

So if you can build queries in the DBF files and if you have an ODBC driver
in Excel to connect to those Queries, you can get the data you want.

Hope that helps.
Dennis
 
O

onedaywhen

There are many ways to achieve this. One which may be of interest is
to use the
'SQL' functions in the ODBC add-in which ships with pre-Office2003
versions of Excel (but can still be download from MSDN, AFAIK). Look
in the help for SQLOpen, SQLClose, SQLExecQuery, SQLRetrieve etc.

If you are happy using VBA code, you will need a data access component
which is beyond the functionality of the VBA library (that's why
Visual Basic for Application doesn't contain suitable functions).
Check out ADO:

http://msdn.microsoft.com/library/d...us/odeopg/html/deovradocomponentlibraries.asp

--
 
Top