none
SQL sp_OAMethod to call VBA Sub in Excel RRS feed

  • Question

  • How does one call a sub (or a function) in VBA (Excel, Word, etc) from T-SQL?

    The third 'EXECUTE' line is causing an error when i try to run the public sub 'SQLRunSimulation'.  This routine exists with out parameters in the workbook.  I have also tried prefixing the module name with the sub.


        EXECUTE @oEvent = sp_OACreate @ProgID, @aXL OUTPUT, @context;

        EXECUTE @oEvent = sp_OAMethod @aXL, 'WorkBooks.Open', @wkb OUTPUT, @wkbPath;

        -- Code to copy SQL data to Excel workbook

        EXECUTE @oEvent = sp_OAMethod @wkb, 'SQLRunSimulation'; -- Errors: Unknown name

        -- Code to copy Excel data to SQL

        EXECUTE @oEvent = sp_OAMethod @aXL, 'ActiveWorkbook.Close', NULL, True;

        EXECUTE @oEvent = sp_OAMethod @aXL, 'Application.Quit';

        EXECUTE @oEvent = sp_OADestroy @aXL



    In the Excel workbook:

    Public Sub SQLRunSPSL()
     <Code>
    End Sub


    Detail Code to retrieve the error message:
        EXECUTE @oEvent = sp_OAMethod @wkb, 'SQLRunSimulation';
       
        IF @oEvent = 0
        BEGIN
            PRINT 'Ran Method for ' + @ProgID
        END;
        ELSE
        BEGIN
            EXECUTE sp_OAGetErrorInfo @o, @src OUT, @desc OUT;
            RAISERROR('Error Running COM Method 0x%x, %s, %s',16,1, @aXL, @src, @desc);
        END;

    Returns an error:
    Msg 50000, Level 16, State 1, Procedure spTest, Line 63
    Error Running COM Method 0xfefefe, ODSOLE Extended Procedure,  Unknown name.

    Thank you for your help...
    _________________________________

    Ps. I can also write a .NET DLL that executes SQLRunSimulation; but I can't register stdole.dll (version/permission issues) and the login is the sysadmin.

    VS 2005
    SQL Server 2008 SP1 Standard
    Win XP (test env) / Server 2003 (prod env)

    Friday, July 10, 2009 7:39 PM

Answers

  • EXECUTE @oEvent = sp_OAMethod @wkb, 'Application.Run', NULL, 'SQLRunSPSL';


    (remark: Some times i need to ask the question out loud to find the answer)
    • Marked as answer by Rogge Friday, July 10, 2009 7:53 PM
    • Edited by Rogge Friday, July 10, 2009 8:20 PM added remark
    Friday, July 10, 2009 7:53 PM