Excel: Send multiple values in "Command text"
Located in the "Data > Connections > Properties > Definition (tab) > Command text", I have the following:
{Call SP_calculo_algo(?)}
Where currently the function receives only one value through the unique parameter that it has, that according with someone told me it is represented by the character of the question mark (?).
What I need is to send two (2) values through the function, since I have the SQL query that returns data that refer to a range between two dates. For example: Start Date (parameter 1) and End Date (parameter 2).
Can you help me?
Answer
Make sure you are using Microsoft Query for your connection.
Data -> From Other Sources -> From Microsoft Query
At the end of the Wizard, make sure you specify "Return data to Microsoft Excel".
After the query executes, the data will be on your worksheet. Click somewhere on the result set, then select the query Data -> Connections -> Properties.
Click on the definition tab. Modify the command text to include the query with question marks as placeholders for your parameters.
Click OK, and you will be prompted for each parameter. Click the box if you want to save the value you enter for future refreshes.
