In my life-changing post, Running a SQL-Stored Procedure from Excel, I showed you how to make Excel interact with a SQL server Stored Procedure.
.
Since that post, I've had several requests to cover the topic of passing dynamic parameters to a Stored Procedure. So today, I will FINALLY show you.
.
Step 1: Data tab – > From Other Sources -> From SQL Server
.
Step 2: Enter Credentials.
.
Step 3: Choose any small table or view. It doesn't matter which one because we'll be changing the connection anyway.
.
Step 4: In the Data Connection Wizard dialog, give your connection a friendly name that you can remember. This is how you'll point to this connection in VBA. In this case, I'm calling this connection MYSERVER.
.
Step 5: Excel will pop up the Import Data dialog box - Click Properties here (NOT THE OK BUTTON).
.
Step 6: Click on the Definition tab. Here, you'll want to change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input. As you can see, the SQL statement is simply a call to the Stored Procedure along with the Parameter name (in this case, the procedure is expecting one parameter that accepts a market name).
.
Step 7: Excel warns you about something unimportant - Click Yes (which in this case, means go away).
.
Step 8: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.
.
Step 9: Note the results you get from your original hard-coded command you entered in Step 6. At this point, you know your connection is working.
.
Step 10: Create a mechanism to dynamically select and enter your parameters.
In my case, I need to pass a market name to my Stored Procedure. So I added a simple dropdown where my selection goes to Cell B2. The mechanism you choose to select your parameters is not the important thing here. The important thing is to note where the final selection will be housed. In this example, my market selection will end up in Cell B2.
.
Step 11: The final step is to copy and paste this Macro into a standard module. Notice that all we are dong here is changing the definition fo the CommandText property of the Connection. This basically means that as long as you can point to the correct connection (MYSERVER in this case), you can rebuild the CommandText on the fly. Here, I'm rebuilding the CommandText to point to my chosen cell range (cell B2 - where my market selection is housed).
With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
.CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value & "'"
End With
ActiveWorkbook.Connections("MYSERVER").Refresh
End Sub
.
At this point, it's just a question of firing the Macro each time your parameter selection changes.
Now you're cooking with gas!
RELATED STUFF
- Running an Access Parameter Query from Excel
- Creating a Zoom Box in Excel
- Smart Scroll Bars in Excel
- Excel Zoom Box Font Size Follow Up
- Building Version Control in Excel
- Running an Excel Macro from Access (or Another Excel Workbook)
- Use Windows Authentication for your Excel and Access Applications
- Recordset Tricks in Excel 1 – Filling a ComboBox with Unique Values
- Recordset Tricks in Excel 2 – Cascading ListBoxes
- Running Crosstab Queries in Excel
One more trick to ask for as a result of this: the parameter-passthrough is great -- but how do I also add an "all" to the parameter set? (or, for that matter, a subset?)
ie in your case rather than simply choosing one of the five, I want some, or all of them reported on.
Thanks - your blog is always an interesting source of Excel fun! (Yes - fun, that's what we number freaks have with a tool like this.)
Ziggy: To be able to get "ALL", you would have to alter the Stored Procedure itself to handle the "All" option.
if @Market - 'All' then
--some statement that returns all records
else
--some statement that uses the @Market paramter.
Once you have altered your procedure, you can simply pass "All" as the Market.
Can't you change the SQL to:
EXECUTE dbo.Tng_Market_Feed '["Enter Market"]'
and have Excel handle the parameter so you can link it directly to a cell?
What a a great procedure Mike thank you!
Jan Karel: I don't think that would work when specifying a connection to SQL.
However, you can do this:
1. In step 1, choose 'From Microsoft Query' instead of 'From SQL Server'.
2. Go through Step 2, 3, and 4 as described in this post.
3. In Step 5, you could use something like this
Exec SP_GetEmployeeManagers ?
The question mark (?) will be understood as a parameter.
From here, the query will ask for the parameter value.
At this point, you can set your parameter to link to a cell.
What does Exec SP_GetEmployeeManagers do and shouldn't it be used in step 6?
Mike,
I've been applying this concept, but have run into a problem with displaying dates. The data source provides "date" information in milliseconds counting from 1/1/1970. Where would you apply the conversion? Is there a way to put it into the SQL? Or would you do the conversion when you do the analysis?
Thanks,
Eric
This example was very helpful. Do you have an example that insert/update/deletes data on SQL Server using a stored procedure?