SQLTeam.com | Weblogs | Forums

Stored procedure


Can you use the information in the cells of an excel spreadsheet as the values for the Stored procedures parameters? 3 of the cells contains multiple values.
If this is possible, please explain how this can be done. If it cant be done, could I possibly use Crystal reports to accomplish this?


Could do with an example of what is in the 3 cells

In case it is the answer to your question we have Excel spreadhseets connected to a SQL DB (using MSQUERY I think) where there is a specific "Parameters" sheet where the user can fill in some values, and those values are passed to an SProc as @Parameters, and then the results that come back from the Sproc populate a second sheet.


Wow that sounds exactly what I need. Could you possibly send me that ? Or let me know what I could send you to make it clearer. Thank you very much


Sorry,. its all proprietary. But just need to fire up a MSQUERY on the sheet - I suggest you start with a simple query

SELECT TOP 10 Col1, Col2, Col3
FROM MyTable

just to get "some data" and see what happens

Then you can graduate to parameterising it using XLS Cells as the parameters (We reserve SHEET-1 for the parameters so they aren't dotted about the sheets / cells etc.)


So this is done in excel? It's done in the sheet of input data ? Where do I insert the code ?


Here's the first result from Google:


Thanks again for the extremely helpful link. I have done everything but get an error. It is code 9 out of bounds. I have attached my VBA code. When you can, can you take a look at the bottom 'with' statement as that is where the debugger says my error occurs.