Need to use Excel function in Access

Fijian

Registered User.
Local time
Today, 12:49
Joined
Sep 22, 2005
Messages
32
Hi,

I am trying to use the YIELD worksheet function in Access but can't get it to work. It comes with Error Sub or function not defined.

As per help file I have installed msowcf.dll but it still doesn't work.

Any ideas?

Thanks
 
I think you simply need do Tools>References and from the list select the Excel Object Library.
 
Then I think you still need to envoke the XL application to get at the functions.

I dont know what a yield function does, cannot you rebuild it in Access?
 
Guys,

Excel Object Liabrary does not work.

Also, it is not a simple equation which can be rebuilt as it takes hundreds of iterations to calculate so that is out of question.

Any other ideas.


Thanks
 
Yes it does I was sure of it... I did a search on this forum Excel formula use in Access
After some reading I found this which solves your problem...

I will quote it to make it even easier for you.... Great feature, this searching...
Jon K said:
If you make a reference to the Microsoft Excel x.x Object Library (when the Access code window is open, choose menu Tools, References... and select the library) you can then use the SumProduct() function in VBA by passing arrays to it, e.g.

MsgBox WorksheetFunction.SumProduct(array1, array2, etc)
.

I.e. WorksheetFunction.Yield
Should enable you to use your Yield function....
 
Hi,

Just to let you know that WorksheetFunction does not work as it is not part of it.

It is a Add On in Excel from Analysis Tool Pack.

Any other ideas anyone??

Thanks
 
Ah, the question then is whether you have a SEPARATE reference to the Excel Analysis Tool Pack. Making a reference to (just plain old) Excel in the module page References list isn't enough in that case.

Also, there is the issue of naming an array as input. I would wonder about this because normally Access doesn't "like" arrays.
 
Fijian said:
Any other ideas anyone??
Sorry fresh out of ideas....

Is it possible to call the Yield function from VBA in Excel??
 
Yes it is possible in Excel.

But not in Access.
 
Can one call an Excel VBA function from Access?
 
It is absolutely possible in Access. In addition to the MS Excel Reference, you need to add a reference to the confusingly named "ATP vX.0 Library" (where X is the version #). Note that depending on how old your Office is, this may also be called "atpvbaen". In Office 2003, it's the 2.0 Library.

For the record, ATP = "Analysis Tool Pack". They're funky like that.

Anyway, to access the functions from the ATP, you have to make a connection to the object like this:

Dim xlATP As OCATP

Then, to get to the YIELD function and all the other functions from the Analysis Tool Pack, you access it like this:

xlATP.Yield (parameters here)

As shown from my Immediate Window:
yield.jpg


Ta da! ;)
 
Last edited:
Thanks Moniker.

Your suggestion appears to do the trick but I am struggling to get the result.

I have tried several ways to get it work but I think I am missing something which could be very simple.

I would appreciate if anyone can have a look and point out what I am missing.

Please click the button "Press Me" on my form.


Thanks
 

Attachments

You need to instantiate the object (Dim xlApp As New ATP2.OCATP) and make sure the DLL is registered. From the Access Help File:

ActiveX component can't create object or return reference to this object (Error 429)

Creating objects requires that the object's class be registered in the system registry and that any associated dynamic-link libraries (DLL) be available. This error has the following causes and solutions:

The class isn't registered. For example, the system registry has no mention of the class, or the class is mentioned, but specifies either a file of the wrong type or a file that can't be found.
If possible, try to start the object's application. If the registry information is out of date or wrong, the application should check the registry and correct the information. If starting the application doesn't fix the problem, rerun the application's setup program.

A DLL required by the object can't be used, either because it can't be found, or it was found but was corrupted.
Make sure all associated DLLs are available. For example, the Data Access Object (DAO) requires supporting DLLs that vary among platforms. You may have to rerun the setup program for such an object if that is what is causing this error.

The object is available on the machine, but it is a licensed Automation object, and can't verify the availability of the license necessary to instantiate it.
Some objects can be instantiated only after the component finds a license key, which verifies that the object is registered for instantiation on the current machine. When a reference is made to an object through a properly installed type library or object library, the correct key is supplied automatically.

If the attempt to instantiate is the result of a CreateObject or GetObject call, the object must find the key. In this case, it may search the system registry or look for a special file that it creates when it is installed, for example, one with the extension .lic. If the key can't be found, the object can't be instantiated. If an end user has improperly set up the object's application, inadvertently deleted a necessary file, or changed the system registry, the object may not be able to find its key. If the key can't be found, the object can't be instantiated. In this case, the instantiation may work on the developer's system, but not on the user's system. It may be necessary for the user to reinstall the licensed object.

You are trying to use the GetObject function to retrieve a reference to class created with Visual Basic.
GetObject can't be used to obtain a reference to a class created with Visual Basic.

Access to the object has explicitly been denied.
For example, you may be trying to access a data object that's currently being used and is locked to prevent deadlock situations. If that's the case, you may be able to access the object at another time.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
________________

Hopefully, that will provide a little bit of a new direction to go in.
 

Users who are viewing this thread

Back
Top Bottom