Need to use Excel function in Access (1 Viewer)

Fijian

Registered User.
Local time
Today, 01:23
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
 

KenHigg

Registered User
Local time
Yesterday, 20:23
Joined
Jun 9, 2004
Messages
13,327
I think you simply need do Tools>References and from the list select the Excel Object Library.
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:23
Joined
Aug 11, 2003
Messages
11,695
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?
 

Fijian

Registered User.
Local time
Today, 01:23
Joined
Sep 22, 2005
Messages
32
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
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:23
Joined
Aug 11, 2003
Messages
11,695
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....
 

Fijian

Registered User.
Local time
Today, 01:23
Joined
Sep 22, 2005
Messages
32
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 28, 2001
Messages
27,317
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.
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:23
Joined
Aug 11, 2003
Messages
11,695
Fijian said:
Any other ideas anyone??
Sorry fresh out of ideas....

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

Fijian

Registered User.
Local time
Today, 01:23
Joined
Sep 22, 2005
Messages
32
Yes it is possible in Excel.

But not in Access.
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:23
Joined
Aug 11, 2003
Messages
11,695
Can one call an Excel VBA function from Access?
 

Moniker

VBA Pro
Local time
Yesterday, 19:23
Joined
Dec 21, 2006
Messages
1,567
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:


Ta da! ;)
 
Last edited:

Fijian

Registered User.
Local time
Today, 01:23
Joined
Sep 22, 2005
Messages
32
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

  • Test Yield.zip
    30.7 KB · Views: 162

Moniker

VBA Pro
Local time
Yesterday, 19:23
Joined
Dec 21, 2006
Messages
1,567
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

Top Bottom