Post reply

How To Get Excel To Use A Stored Procedure's Result Set As Its Data Source

  • July 7, 2008 at 11:02 am

    #209652

    I have a multi-sheet Excel file (Sheet1) that uses a SELECT query (MS query) to pull in and display SQL Server data; and though this seems to work well enough for what it is, what I really need is for the query to call and run a stored procedure, passing the value of a specific cell on Sheet2 as a parameter, and then using the result set of the SP as the datasource of the spreadsheet.

    I'm at a complete loss as to how to get Excel (or SQL) to do this. Does anybody know how to do this?

    Thanks

    djs

  • July 7, 2008 at 11:15 am

    #839052

    We've actually got an article Q'd up for Aug 4th on this.

    the author used the "Edit" query button after setting up a regular query, then entering the stord procedure name and that didn't work. next a Macro was used as well as Microsoft Query.

    Macro here:

    Sub RefreshSheet()

    '

    ' RefreshSheet Macro

    ' Macro recorded 05/06/2008 by David Poole

    '

    Set qt = Sheets("sheet1").QueryTables(1)

    qt.Sql = "exec uspGetEmployeeManagers ?"

    Set param1 = qt.Parameters("Enter the employee ID")

    ' The equivalent of the "Get the value from the following cell" portion of the parameters dialogue.

    param1.SetParam xlRange, Range("sheet1!a1")

    ' The equivalent of the check box "Refresh automatically when cell value changes".

    param1.RefreshOnChange = True

    qt.Refresh

    ' If you instantiate objects destroy them afterwards.

    Set param1 = Nothing

    Set qt = Nothing

    End Sub

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 2 posts - 1 through 1 (of 1 total)

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