DaFou
asked on
Excell Financial Analisis Functions from Access VBA
Ola,
In Excell I have made use of the Yield function that resides in the financial Analisis add in.
When I remove the add in the worksheet still works but the function call now is changed to the following:
='C:\Program Files\Microsoft Office\OFFICE11\Library\An
In Access 97 I tried to make use of this Yield function in excell using the following code
Public Sub Tester()
Dim lDblYield As Double
Dim obj As Object
Set obj = CreateObject("Excel.Applic
lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
obj.Quit
Set obj = Nothing
MsgBox lDblYield
End Sub
But it breaks as It cant finds the function. Now how do I make Sure that the excell object I just instantiated recognises the Yield function that is available through the financial analisis add in?
In Excell I have made use of the Yield function that resides in the financial Analisis add in.
When I remove the add in the worksheet still works but the function call now is changed to the following:
='C:\Program Files\Microsoft Office\OFFICE11\Library\An
In Access 97 I tried to make use of this Yield function in excell using the following code
Public Sub Tester()
Dim lDblYield As Double
Dim obj As Object
Set obj = CreateObject("Excel.Applic
lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
obj.Quit
Set obj = Nothing
MsgBox lDblYield
End Sub
But it breaks as It cant finds the function. Now how do I make Sure that the excell object I just instantiated recognises the Yield function that is available through the financial analisis add in?
okI did some testing...
If Analysis ToolPak is not availible (not installed when Excel was installed), Excel (2000) prompts:
"Microsoft Excel can't run this add-in.
This feature is not currently installed. Would you like to install it now?"
The rest is trivial: if you dont install the addin the .xls sheet wont open and if you do install then it works.
If Analysis ToolPak is not availible (not installed when Excel was installed), Excel (2000) prompts:
"Microsoft Excel can't run this add-in.
This feature is not currently installed. Would you like to install it now?"
The rest is trivial: if you dont install the addin the .xls sheet wont open and if you do install then it works.
ASKER
Dim objATP As AddIn
Breaks :-(
User-defined type not defined
So you sugestion will make this work eventually:
Dim objATP As AddIn
Set objATP = AddIns("Analysis ToolPak")
If Not objATP.Installed Then
AddIns("Analysis ToolPak").Installed = True
End If
Dim lDblYield As Double
Dim obj As Object
Set obj = CreateObject("Excel.Applic
lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
obj.Quit
Set obj = Nothing
MsgBox lDblYield
?
Breaks :-(
User-defined type not defined
So you sugestion will make this work eventually:
Dim objATP As AddIn
Set objATP = AddIns("Analysis ToolPak")
If Not objATP.Installed Then
AddIns("Analysis ToolPak").Installed = True
End If
Dim lDblYield As Double
Dim obj As Object
Set obj = CreateObject("Excel.Applic
lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
obj.Quit
Set obj = Nothing
MsgBox lDblYield
?
ASKER
This installs the add in in the excell object. But why cant I use its Yield function?
Public Sub Tester()
Dim lDblYield As Double
Dim lObjExcel As Object
Set lObjExcel = CreateObject("Excel.Applic
Dim lObjAddIn As Object
Set lObjAddIn = lObjExcel.AddIns("Analysis
If Not lObjAddIn.Installed Then
lObjAddIn.Installed = True
End If
lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)
lObjExcel.Quit
Set lObjExcel = Nothing
MsgBox lDblYield
End Sub
Public Sub Tester()
Dim lDblYield As Double
Dim lObjExcel As Object
Set lObjExcel = CreateObject("Excel.Applic
Dim lObjAddIn As Object
Set lObjAddIn = lObjExcel.AddIns("Analysis
If Not lObjAddIn.Installed Then
lObjAddIn.Installed = True
End If
lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)
lObjExcel.Quit
Set lObjExcel = Nothing
MsgBox lDblYield
End Sub
ASKER
The code breaks here:
lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)
Object does not support this property or method
lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)
Object does not support this property or method
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
srparr,
in the Yield part of the code examples the following line breaks
objXL.Workbooks("atpvbaen.
variable xlAutoOpen undefined..
in the Yield part of the code examples the following line breaks
objXL.Workbooks("atpvbaen.
variable xlAutoOpen undefined..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am sorry but ill need a working version that works in access 97
THe code you provided breaks at the same line telling me the xlAutoOpen variable is not defined
So I changed the xlAutoOpen for an Integer 1
Like this
Public Sub TestIt()
MsgBox fXLYield("2/15/2008", "11/15/2016", 557, 95.04287, 100, 2, 1)
End Sub
Public Function fXLYield(dtmSettlement As Date, dtmMaturity As Date, dblRate As Double, dblPR As Double, dblRedemption As Double, bytFrequency As Byte, bytBasis As Byte) As Double
Dim objXL As Object
Set objXL = CreateObject("Excel.Applic
objXL.Workbooks.Open (objXL.Application.Library
objXL.Workbooks("atpvbaen.
fXLYield = objXL.Application.Run("atp
End Function
But now it breaks on the last statement telling me:
Type Mismatch
any ideas?
THe code you provided breaks at the same line telling me the xlAutoOpen variable is not defined
So I changed the xlAutoOpen for an Integer 1
Like this
Public Sub TestIt()
MsgBox fXLYield("2/15/2008", "11/15/2016", 557, 95.04287, 100, 2, 1)
End Sub
Public Function fXLYield(dtmSettlement As Date, dtmMaturity As Date, dblRate As Double, dblPR As Double, dblRedemption As Double, bytFrequency As Byte, bytBasis As Byte) As Double
Dim objXL As Object
Set objXL = CreateObject("Excel.Applic
objXL.Workbooks.Open (objXL.Application.Library
objXL.Workbooks("atpvbaen.
fXLYield = objXL.Application.Run("atp
End Function
But now it breaks on the last statement telling me:
Type Mismatch
any ideas?
have you set the recerence to excel in the VBA tools, references microsoft excel 10 library?
ASKER
It also fails on access 2003.
The last line type mismatch
The last line type mismatch
ASKER
Sudonim,
Yes I have set a reference now and am no longer using
objXL.Workbooks("atpvbaen.
WIth the reference using the following works fine
objXL.Workbooks("atpvbaen.
But still somehow my parameters that i pass are not of the right type or something.
Yes I have set a reference now and am no longer using
objXL.Workbooks("atpvbaen.
WIth the reference using the following works fine
objXL.Workbooks("atpvbaen.
But still somehow my parameters that i pass are not of the right type or something.
indeed, looks very tricky. its beyond my field of experience, but i knew te reference bein set would assist vb in recognising the xlautopen constant...
is tere anyway you can break down your processes into smaller chunks and check each aspect is working...?
is tere anyway you can break down your processes into smaller chunks and check each aspect is working...?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Create a new module (or use an existing one) and make new Sub
Sub Auto_Open()
' ----- Loads the "Analysis ToolPak" -addin when document is opened
Dim objATP As AddIn
Set objATP = AddIns("Analysis ToolPak")
If Not objATP.Installed Then
AddIns("Analysis ToolPak").Installed = True
End If
End Sub
This way Excel automaticly loads the addin when document is opened. You could also create a Sub Auto_Close() where you can unload the addin when document is closed:
Sub Auto_Close()
' ----- UnLoads the "Analysis ToolPak" -addin when document is closed
Dim objATP As AddIn
Set objATP = AddIns("Analysis ToolPak")
If objATP.Installed Then
AddIns("Analysis ToolPak").Installed = False
End If
End Sub
Well, if the .xls sheet is moved to a computer where Analysis ToolPak is not availible, I really dont know how you can load it then. Maybe you have to copy the .xla also?