Post reply

Call Excel functions from SQL stored procedure

  • November 17, 2004 at 8:57 am

    #60441

    Does anyone know of a way or a resource to call an Excel function from a SQL stored procedure?

    I've created a stored procedure to mimic an excel financial function, but it takes several minutes rather than seconds. If I can call the Excel function or pass a temporary table to Excel to perform the function and return a single value, that would help tremendously.

    Thanks!

  • November 17, 2004 at 10:28 am

    #530516

    Best way is to create an instance of excel within an activex script in a dts package, open the excel document, do the logic and then write back the results into a temp table or a global variable within the dts package.

     

    Go to http://www.sqldts.com if you want to get a good starting point on dts packages if you know little about them.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • November 17, 2004 at 12:24 pm

    #530537

    What "financial function" are you talking about?

    There are quite a few that can easily be transferred to T-SQL. And then you can use the sp_OA* procedures to instanciiate them as COM objects. You might want to search the Google archives for this, since SQL Server MVP Steve Kass posted quite a few examples on how to do this.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 17, 2004 at 3:49 pm

    #530560

    Thanks to you both for your quick replies.

    I'm trying to use the XIRR function from Excel. I created a stored procedure which produces the same result as the function, given the same input, but it takes minutes to itterate enough times to get an accurate result.

    Do you know of existing XIRR SQL code?

    I'll check the website for the DTS info. Thanks.

  • November 18, 2004 at 12:59 am

    #530586

    I for one wouldn't do any Internal Rate of Return calculations with SQL. Why?

    Read this and decide for yourself

    http://www.intelligententerprise.com/online_only/celko/030303_1.jhtml

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 18, 2004 at 7:23 am

    #530659

    I saw something similar. That's why I want to do it with Excel. The IRR calc has to be done off data in a SQL database and referenced in a Crystal Report.

    What fun...

  • November 18, 2004 at 7:32 am

    #530664

    Here's some code I use to run an excel macro called update once my datawarehouse has finished populating.  Use the macro to call the function you want.

    Function Main()

            Dim objExcel , i

           

            Set objExcel = CreateObject("Excel.Application")

            With objExcel.FileSearch

                  .LookIn = "\\<<Folder Path>>\"

                    .SearchSubFolders = True

                  .FileName = "*.xls"

                  If .Execute > 0 Then

                            For i = 1 to .FoundFiles.Count

                                    On Error Resume Next

                                    objExcel.Workbooks.Open .FoundFiles(i)

                                    objExcel.Run "Update"

                                    objExcel.ActiveWorkbook.Close(FALSE)

                            Next

                  End If

            End With               

     

            Main = DTSTaskExecResult_Success

    End Function

  • November 18, 2004 at 9:19 am

    #530690

    you have the option of creating a query in Excel or use DTS transform that brings the data into Excel. Then you can use any Excel function you want.

    NOTE: You can't import more then 65K rows into Excel.

     

    Good Luck.

    Don't count what you do, do what counts.

    SQL Draggon

  • November 18, 2004 at 9:07 pm

    #530760

    Have you tried using Report Servises. Thats if you can afford .net 2003. I have read it is possible to interact with excel.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply