Syntax to import Excel function into access VBA. It contains an sql statement as well

Posted on 2007-10-03
Last Modified: 2013-11-27
I am trying to use XIRR function from excel in Access vba.
I have this code
Option Compare Database

Private Sub Command6_Click()

Dim obj As Excel.Application
Dim sql As String
Dim SQL2 As String
Set obj = CreateObject("Excel.application")
sql = "select Indices.Tdate " & _
           "from indices " & _
           "where [Tdate] Between #" & Forms!form77!tstart & "# And #" & Forms!form77!tstart & "# " & _
           "and [Index]= '" & Forms!form77!index1 & "'; "

obj.Workbooks.Open (obj.Application.LibraryPath & "\Analysis\atpvbaen.xla")
obj.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
MsgBox obj.Application.Run("atpvbaen.xla!XIRR", sql, 2)
Set obj = Nothing
End Sub

This code is apparently wrong. But the thing I want to do is on
MsgBox obj.Application.Run("atpvbaen.xla!XIRR", sql, 2)
line. I want to put the Index Close values which is into the arg 1 space. the structure of  XIRR(Values,Dates,guess) is like this. so the user will enter into the form 3 values. which are forms!form77!index1(this is to determine which index in the table is going to be used), forms!form77!tstart(this is the desired starting date), forms!form77!tend(this is the desired end date. So the XIRR function should be able to retrieve the desired daha from the "Indices" table.Then get the values from the "Close" field in the "Indices" table. This information is put into the first argument space in XIRR. The second space is all the values between the desiren start and end date. This information is put into the second argument space. and the final argument space is for the guess. this can  just be a number lets say 0.1.
So briefly speaking, I need to put these necessary sql statement into the XIRR function
Question by:awesomejohn19
    LVL 10

    Accepted Solution

    I'm not sure how to use a SQL statement but in the past, i've found it easier to create a two dimentional arrya (arWorksheet(x,y) ) who's structure matches the worksheet. Then you just have to loop through the entire worksheet and grab all the data. Then you are free to reference anything you need using the x,y coordinates in code.

    This is also a quicker method than re-quering the worksheet over and over again

    Author Comment

    I see your point but who can you code it?
    LVL 10

    Expert Comment

    LVL 1

    Expert Comment

    Forced accept.

    Community Support Moderator

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now