Having stored procedure output go to excel document w/ Parameters

[BaneNeo]

To preface my question I searched this forum up and down for about an hour to try and find the answer to this question and while I found other people asking the same thing I couldn't find the solution so any and all help is appreciated greatly.

This question is for accessing a SQL server in Excel 97-2003 program.

Problem:

I need to run a stored procedure from excel and have the output drop in to the active sheet. I also need to pass in a value from this active sheet to the stored procedure in the form of a local variable (defined within the stored procedure as @date).

How far I have gotten:

I can get the stored procedure to output to excel using Microsoft Query by using the following code:
EXEC storedprocedure1
@date = '2007-05-17'
The problem I am having is setting @date to get it's value from a cell in the sheet. I tried setting "@date = ?" to trigger the parameter functionality in MS Query but it throws an error saying I can't use parameters in queries that can't be represented graphically. (stupid limitation in my opinion)

As a stop gap I tried to set "@date = DATEADD(dd, -1, GETDATE())" . So that the query would be semi-dynamic by setting @date to the day before today but that threw an error in MS Query even though that works fine in Visual Studio )= .

Question:

Is there a way to set @date to acquire it's value from a cell in the sheet from MS Query?

Barring that is there another way to do this so I can pass in a parameter from the sheet to the stored procedure?

I have heard people say in this forum that I might need to use the VBScript background enviroment to do this if so could I get some sample code for connecting to the SQL server, executing the stored procedure and setting the variable to get a value from a cell?

Thanks in advance.

Adrian

 

[SkipVought]

Select in the query table on the sheet.

Data>Import External Data>PARAMETERS...

Skip,
[sub]
[red][/red]
[/sub]

 

[BaneNeo]

The Parameters selection is greyed out so I can't select it.

I checked while selecting a cell in the output and while selecting a cell outside an output with the same result.

 

[SkipVought]

If the Parameters selection is greyed out, it means that your query is not parameterized, probably because its a stored procedure.

If you code the query DIRECTLY in MS Query, you can do the Parameter thing.


Skip,
[sub]
[red][/red]
[/sub]

 

[BaneNeo]

The problem with this is that my query when typed out requires the use of two unions to do it in one query. And since unions can not be represented graphically in MS Query I couldn't use parameters.

I tried it and it threw the same error as with the stored procedure. )=

 

[SkipVought]

There is a work around, if you are to pursue it in VBA. The coding is VERY MINIMAL.

If you wish to try this approch, post you question in Forum707.

Skip,
[sub]
[red][/red]
[/sub]

 

[MsChelle]

Try changing your sql in Query to read:

{Call storedprocedure1(?)}

(with comma delimited ? for each input parameter)