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

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

Show quoteHide quote

Time Difference Calculations
Customers & Phones
Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum