By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,482 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,482 IT Pros & Developers. It's quick & easy.

Using Excel Functions from within Access

100+
P: 119
Hi there. I need to perform a number of financial calculations within a database I am creating. Rather than writing my own functions, I figured it would be worthwhile to use the functions that already exist within excel.

I have found sample code on the web for calling an excel function (being new to Access I tend to borrow bits and pieces from anywhere and everywhere):

Expand|Select|Wrap|Line Numbers
  1. Function fXLCoupDayBS(dtmSettlement As Date, dtmMaturity As Date, frequency As Integer, basis As Integer) As Long
  2. '   Uses the XL function DAYS360 to return the number of days between 2 dates based on a 360 day year.
  3. '   Accepts:
  4. '   dtmStart - the start date
  5. '   dtmEnd - the end date
  6. '   blnMethod - whether to use the US or European Method in the calculation
  7. '   Returns:
  8. '   a string containing the converted number
  9.     On Error GoTo E_Handle
  10.     Dim objXL As Excel.Application
  11.     Set objXL = CreateObject("Excel.Application")
  12.     If IsMissing(blnMethod) Then blnMethod = False
  13.     fXLCoupDayBS = objXL.WorksheetFunction.COUPDAYBS(dtmSettlement, dtmMaturity, frequency, basis)
  14. fExit:
  15.     objXL.Quit
  16.     Set objXL = Nothing
  17.     Exit Function
  18. E_Handle:
  19.     MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
  20.     Resume fExit
  21. End Function
However, I am not quite sure how to call this from Access. Ideally, I would like the result of the function (an integer) to appear in the same row as the inputs to the function, and all of these to be in the same query.

In short, I am not sure how to call a function from a query, passing in the arguments of the function from the query. Can anyone help? Again, if anyone can suggest a better method I am all ears!
Dec 8 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,602
You don't need to open Excel (as in posted code) to access the Excel library.
Just add the Excel library to the References under Tools of the VBA window.
However, I'm pretty sure you can't access objects and/or their methods from within SQL.
Global (Public) functions can be used but nothing else :(.
objExcel.WorksheetFunction.* functions are, therefore, unavailable.
Dec 8 '06 #2

100+
P: 119
Hmmm...So if I can't access the function from within an SQL statement, how should I execute the code?

I have two tables:

Security and
TradeInfo

For a particular Trade (defined within TradeInfo) of a Security I need to calculate accrued interest. This takes information from the two tables, and uses the function outlined above. I am able to execute the function and calculate the accrued interest by calling the function from an Event Procedure on the TradeInfo form. Is there a better way?

Would it then be possible to insert this data into an empty field within the TradeInfo table, or should I enter the data into a new table, and link the two with a query?
Dec 11 '06 #3

NeoPa
Expert Mod 15k+
P: 31,602
Basically it's not accessible to SQL.
However, you could define a global (Public) function in your database that could be called by the SQL code.
This could access the Excel Worksheet functions and return the result you need.
Encapsulation, if you like.
Dec 11 '06 #4

100+
P: 119
So I can called a VBA sub-routine or function from an SQL query? Do you know the format? I have been searching for the syntax but can't find anything on it.
Dec 11 '06 #5

NeoPa
Expert Mod 15k+
P: 31,602
So I can called a VBA sub-routine or function from an SQL query? Do you know the format? I have been searching for the syntax but can't find anything on it.
Expand|Select|Wrap|Line Numbers
  1. Public Function YourFunctionName(...) As Variant
  2.     ...
  3.     YourFunctionName = <Value to be returned>
  4.     ...
  5. End Function
Dec 12 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.