Use Excel Worksheet Function COUPPCD and COUPNCD

I want use Excel worksheet functions COUPPCD and COUPNCD in my VBA code for Access. I have created the Excel object and use its "worksheetfunction" method to call COUPPCD and COUPNCD. But it fails. Can someone help me to solve the problem? Or if there is equivalent functions in VBA or Access?
StanleyLMWAsked:
Who is Participating?
 
tbsgadiConnect With a Mentor Commented:
In your function after the above code try something like

MyFunc  = XlsObj.Application.Run("atpvbaen.xla!COUPPCD ", Param1, Param2,Param3,Param4)

Makesure that the params are of the right data type
0
 
tbsgadiCommented:
Hi,
Make sure you have a reference to the atpvbaen.xla something like this:

 Set XlsObj = New Excel.Application
   XlsObj.Workbooks.Open (XlsObj.Application.librarypath & _
       "\Analysis\atpvbaen.xla")
     
      ' Runs the AutoOpen macro in the add-in
  XlsObj.Workbooks("atpvbaen.xla").RunAutoMacros (1)


Good Luck!

Gary
0
 
StanleyLMWAuthor Commented:
THX
After using the code you have provided, it still doesn't work. VBA reports that the object (which is Excel object) does not support the method COUPPCD or COUPNCD
0
 
StanleyLMWAuthor Commented:
great it is done
0
 
tbsgadiCommented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.