Using Excel Functions in a Query

[Karim]

Hi,
I have a large table containing several years' hourly climate data
(temperature, humidity, etc...). I want to perfom calculations to convert the
temperature unit (from celcius to fahrenheit) and to do some psychrometric
calculations on the data.
I usually do these in excel and I have an excel add-in to do the
psychrometric calculations (some custom VBA functions)
how can I call these functions from within a query and create new fields
with the calculated values?
any help will be great

 

[DAVID]

Copy the custom vba functions into the access database.

Functions in the Access database can be called directly
from queries that you run in Access.

If you call out to Excel, everything will be much
slower and flakier, but it is still possible. To do
that, create functions in the Access database to
call the Excel vba functions. You can call the Access
VBA functions directly from queries that you run
in Access, and each function can then call your
Excel VBA function. Ask in an Excel group for
more information on calling Excel VBA functions
from VB script, it's done the same was from Access.

(david)

 

[Steve]

Add Excel to the references in the database and you can use Excel functions
in the database. BTW, your calculations are very simplistic and would be a
breeze in Access alone.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Karim]

Thank you guys for the quick answers.
What I don't know is how to copy the vba functions from excel to access and
make them available in queries.
They were installed as add-ins.
Also, I don't know how to add excel reference in the database. Is this
reference going to be global? meaning will I be able to see all the functions
in the query expression builder?
By the way, these calculations have a constants table with them where they
pickup values corresponding to inputs from the function
Thanks