Using excel functions in access

  • Thread starter Thread starter jer
  • Start date Start date
J

jer

I am not sure to whom I should be addressing this so I am
sending it here and also the modulesdaovba group. I saw
recently in the excel group where someone was asking
about a function that would covert dollar values in
figures to text values (e.g. 100.10 would return One
Hundred Dollars and Ten Cents) and was referred to a site
with this and other functions. Is there a way to use
this function in access. I recall sometime ago some help
being offered in using another of execl's built in
functions - days360()
thanks
jer
 
Sure it can be done.
But you would be better off asking for the Access VBA equivalent function!
Many have been written including the numbers to text function. (I have seen
it many times over the years, try Google or just re-post and ask for it!)

========================
To answer the original question:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

Originally posted by Max Dunn
 
Thanks Joe
will do
jer
-----Original Message-----
Sure it can be done.
But you would be better off asking for the Access VBA equivalent function!
Many have been written including the numbers to text function. (I have seen
it many times over the years, try Google or just re-post and ask for it!)

========================
To answer the original question:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as
Click to expand...
Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q1 98571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

Originally posted by Max Dunn

--
Joe Fallon
Access MVP



jer said:
I am not sure to whom I should be addressing this so I am
sending it here and also the modulesdaovba group. I saw
recently in the excel group where someone was asking
about a function that would covert dollar values in
figures to text values (e.g. 100.10 would return One
Hundred Dollars and Ten Cents) and was referred to a site
with this and other functions. Is there a way to use
this function in access. I recall sometime ago some help
being offered in using another of execl's built in
functions - days360()
thanks
jer
Click to expand...


.
Click to expand...
 
Back
Top