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

• deekadelic

  Ten Centuries

  Points: 1123

  More actions

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737981

  More actions

  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