Direcionem o arquivo para o ANALYS32.XLL 2003
objExcel.RegisterXLL caminho & "\ANALYSIS\ANALYS32.XLL"
veraslepo wrote:
Sorry, Ralph, but still doesn't work.I can't write objExcelWorksheet.
18-fev-08
Sorry, Ralph, but still doesn't work.
I can't write objExcelWorksheet.Function, because it's another run-time error.
Neither works without the extra period and parenthesis.
It makes me crazy
:
Previous Posts In This Thread:
On quarta-feira, 13 de fevereiro de 2008 10:58
veraslepo wrote:
Excel XIRR function in Access 2007
I'd like to be able to use the Excel XIRR financial functions in Access 2007.
Tried in several ways, but doesn't work (error 1004):
If CodiceCorrente <> RstOrig!ID Then
RstDest![Rendimento annuo %] =
objExcel.Application.Run("atpvbaen.xlam!XIRR", Valore_Flusso(), Data_Flusso())
I've tried also:
'RstDest![Rendimento annuo %] =
objExcel.WorksheetFunction.Xirr(Valore_Flusso(), Data_Flusso())
but doesn't work the same.
Any suggestions?
Thanks & Best regards
On quarta-feira, 13 de fevereiro de 2008 15:25
Ralp wrote:
The function takes 3 parameters, Values, Dates, GuessI don't see Guess in your
The function takes 3 parameters, Values, Dates, Guess
I do not see Guess in your code.
:
On quarta-feira, 13 de fevereiro de 2008 15:33
Ralp wrote:
My mistake Guess is optional.
My mistake Guess is optional. It may be the way you are passing your
variables to the arrays. How are Valore_Flusso(), Data_Flusso() created?
:
On quinta-feira, 14 de fevereiro de 2008 3:23
veraslepo wrote:
Dim Valore_Flusso(100) As Double, Data_Flusso(100) As DateAre they wrong?
Dim Valore_Flusso(100) As Double, Data_Flusso(100) As Date
Are they wrong?
:
On quinta-feira, 14 de fevereiro de 2008 8:34
Ralp wrote:
That should work as long as the first variable in the Valore_Flusso array is a
That should work as long as the first variable in the Valore_Flusso array is
a negative number. Example function below works.
Public Function dblXIRR() As Double
Dim xl As New Excel.Application
Dim strValues() As String
Dim varDates(2) As Variant
strValues = Split("-15000,10000,22000", ",")
varDates(0) = CDate("01/01/2008")
varDates(1) = CDate("01/01/2009")
varDates(2) = CDate("01/01/2010")
dblXIRR = xl.WorksheetFunction.Xirr(strValues, varDates)
End Function
:
On sexta-feira, 15 de fevereiro de 2008 3:29
veraslepo wrote:
Sorry, but still doesn't work...
Sorry, but still doesn't work...
Here it is the full code
Public Function TIR(QueryOrig As String, TabDestinaz As String)
'Funzione che calcola il tasso interno di rendimento di un flusso di cassa
'Richiede in ingresso una query che deve contenere i campi ID, Importo e
Data e fornisce in output una tabella con i campi ID e Rendimento annuo %
'Richiede l'aggiunta della libreria "Microsoft Excel 11 Object Library"
Dim tdf As DAO.TableDef
Dim RstDest As DAO.Recordset
Dim fld As DAO.Field
Dim RstOrig As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Valore_flusso(100) As Double, Data_Flusso(100) As Date
Dim CodiceCorrente
Dim objExcel As New Excel.Application
Set objExcel = CreateObject("Excel.Application")
' Apre la libreria aggiuntiva di excel
'objExcel.Workbooks.Open (objExcel.Application.LibraryPath &
"\Analysis\atpvbaen.xla")
' Lancia la macro automatica
'objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = TabDestinaz Then
db.TableDefs.Delete TabDestinaz
Exit For
End If
Next
Set tdf = db.CreateTableDef(TabDestinaz)
Set fld = tdf.CreateField("ID", dbLong): tdf.Fields.Append fld
Set fld = tdf.CreateField("Rendimento annuo %", dbDouble):
tdf.Fields.Append fld
db.TableDefs.Append tdf
Set RstDest = db.OpenRecordset(TabDestinaz, dbOpenTable)
Set qdf = db.QueryDefs(QueryOrig)
Set RstOrig = qdf.OpenRecordset
RstOrig.MoveFirst
RstDest.AddNew
RstDest!ID = RstOrig!ID
CodiceCorrente = RstOrig!ID
For j = 0 To 100
Valore_flusso(j) = 0
Next j
For j = 0 To 100
Data_Flusso(j) = 0
Next j
i = 0
Do While Not RstOrig.EOF
If CodiceCorrente <> RstOrig!ID Then
'RstDest![Rendimento annuo %] =
objExcel.Application.Run("atpvbaen.xla!XIRR", Valore_Flusso(), Data_Flusso())
RstDest![Rendimento annuo %] =
objExcel.WorksheetFunction.Xirr(Valore_flusso(), Data_Flusso())
RstDest.Update
RstDest.AddNew
RstDest!ID = RstOrig!ID
CodiceCorrente = RstOrig!ID
For j = 0 To 100
Valore_flusso(j) = 0
Next j
For j = 0 To 100
Data_Flusso(j) = 0
Next j
i = 0
End If
Valore_flusso(i) = RstOrig!Importo
Data_Flusso(i) = RstOrig!Data
i = i + 1
RstOrig.MoveNext
Loop
RstDest![Rendimento annuo %] =
objExcel.Application.Run("atpvbaen.xla!XIRR", Valore_flusso(), Data_Flusso())
RstDest.Update
objExcel.Quit
Set objExcel = Nothing
End Function
What's wrong?
:
On sexta-feira, 15 de fevereiro de 2008 10:53
Ralp wrote:
RE: Excel XIRR function in Access 2007
Try changing the following line:
RstDest![Rendimento annuo %] =
objExcel.Application.Run("atpvbaen.xla!XIRR", Valore_flusso(), Data_Flusso())
to
RstDest![Rendimento annuo %] =
objExcelWorksheet.Function.Xirr(Valore_flusso(), (), Data_Flusso())
If that does not work have you tried copying the values from the 2 arrays
into 2 columns in an Excel worksheet then trying the xlirr function on those
values?
:
On sexta-feira, 15 de fevereiro de 2008 10:58
Ralp wrote:
sorry I added an extra period and parenthesis to objExcelWorksheet.Function.
sorry I added an extra period and parenthesis to
objExcelWorksheet.Function.Xirr(Valore_flusso(), (), Data_Flusso())
should be
RstDest![Rendimento annuo %] =
objExcel.WorksheetFunction.Xirr(Valore_flusso(), Data_Flusso())
:
On segunda-feira, 18 de fevereiro de 2008 3:26
veraslepo wrote:
Sorry, Ralph, but still doesn't work.I can't write objExcelWorksheet.
Sorry, Ralph, but still doesn't work.
I can't write objExcelWorksheet.Function, because it's another run-time error.
Neither works without the extra period and parenthesis.
It makes me crazy
:
EggHeadCafe - Software Developer Portal of Choice
ASP.NET Remote Scripting, Yes! AJAX, NOT!
http://www.eggheadcafe.com/tutorial...bec-413cc4725d38/aspnet-remote-scripting.aspx