EXCEL / SQL SERVER HELP - URGENT

M

MS User

I got a stored procedure with 1 parameter and I want to call this stored
procedure inside an Excel Query ,

I tried

..dbo.MySP ?

It return with error message
"Parameters are not allowed in queries that can't be displayed graphically"

The purpose for this approach -

This would allow me to anchor my parameter to certain cells on the
spreadsheet.
My report users could then change the values in those cells and click a
button to refresh the query data.

Thanks
Mike
 
R

Rich

As long as you are going to use a button, you might try using code like ADO
(using a visual basic control button that is - get from View/Toolbars):

Sub GetData()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim DateVar As Date

DateVar = Sheets("Sheet1").Range("A1")
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourServer;" _
& "Initial Catalog=yourDB;UID=SA;PWD=Tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc

cmd.CommandText = "stp_yourSP"
cmd.Parameters("@bDate").Value = DateVar
Set RS = cmd.Execute
Sheets("Sheet1").Range("A2").CopyFromRecordset RS
End Sub

The user enters a date value Cell("A1") and then clicks the button. Boom!
starting at Cell("A2") you have your resultset.

HTH
Rich
 
Top