July 7, 2008 at 11:02 am
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
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply