Friend someone know execute a sp with parameters in excell but that excell asks them??
-
Hello friend i need to execute a sp in excell i am trying with microsoft query but i dont know how to do to excell asks for them?
my sp need two parameters one start date and a end date
exec cp_reportConsult @dtStart, @dtEnd
the results of the execution i needed to do a Dynamic Table,
but i dont know how to do when i update the datas of table , Excell asks for the input parameters ( the dates) someone know to do that? thanks for your helpings
-
This was removed by the editor as SPAM
-
More details would be appreciated. As much I can understand, u have a sql sp with 2 input params that needs to access an excel function. Is this the case?
-
I believe he is using MS Excel to access data in SQL Server. Rather than have MS Query run a SQL query directly, he is having MS Query execute a stored proc that (presumably) performs some data manipulation and returns back a resultset. This stored proc takes two date parameters. Currently MS Excel is prompting the user for the two date parameter values at run time. I presume he would rather the values of those data parameters come from values in cells of the current spreadsheet. Is this correct Hector?
I have Excel 2003. In the data menu there's an item called "Import External Data". There's a menu item called "Parameters" in here - this lead me to search in Office help for "parameters". What you want to do is in the help topic entitled "Customize a parameter query" - one of the headings in the text is called "Use data from a cell as a parameter value". It seems pretty straightforward. I should also point out that the problem would occur regardless of you using a stored proc as you can parameterise standard MS Query queries. The query you are executing is SQL code which happens to call a stored proc rather than run a SQL select statement directly.
Good luck - let us know how it works out. Cheers, Ian
-
Yes, you understood my problem, thanks friend i will try your wise, although the export external data needs to configure a sql driver, for me is not very optimal, because i will need to configure that driver for each computer , i think to create a VB Macros is the best solution, thanks
i learnt a new thing to day -
I developed a simple excel spreadsheet that I use for UNIT testing that would be a good demonstration how to implement sp calls in VB.
Basically there are three sheets, one to enter the server and DB details, one to enter proc calls and one for results. I will send it to you if you like.
SQL guy and Houston Magician
-
{call cp_reportConsult(?,?)} in SQL pane of Microsoft query.
-
Thanks friends i have resolved my problem,finally i did a Excel Macros, with a userform which one inputs the parameters, then using the next code to update my pivottable
ActiveSheet.PivotTables(1).TableRange2.Select
Workbooks(1).ActiveSheet.PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=Array(Conexion, sQuery)
where sQuery is built with my sp sQuery = " exec cp_report '200601010,'20070101'
well if someone need my macros can send me a email to response it
thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply