Post reply

Friend someone know execute a sp with parameters in excell but that excell asks them??

  • December 29, 2006 at 7:38 am

    #174260

    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

  • January 1, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • January 1, 2007 at 8:29 pm

    #680687

    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?

  • January 1, 2007 at 10:20 pm

    #680691

    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

  • January 2, 2007 at 5:35 am

    #680723

    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

  • January 3, 2007 at 7:56 am

    #680963

    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

  • January 9, 2007 at 12:35 am

    #681957

    {call cp_reportConsult(?,?)} in SQL pane of Microsoft query.

  • January 9, 2007 at 10:41 am

    #682099

    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