Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

1. Open Excel
2. Select the Data Tab (If you’re using Excel 2007 onwards!)
3. Select the “From Other Sources” drop down box
4. Select “From SQL Server”
5. It will open up the following box
   
   
   
6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
8. Hit “Next >”
9. You should then see the following screen:
   
   
   
10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
11. In the list at the bottom, select a table, it doesn’t matter which you choose.
12. Press “Next >”
13. You should then be presented with the following screen:
    
    
    
14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
16. In the “Description” enter something meaningful for this connection
17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
18. Hit “Finish”
19. You should then be shown the following screen:
    
    
    
20. Click “Properties...”
21. The “Connection Properties” window will be shown:
    
    
    
22. Click the “Definition” tab, which will show:
    
    
    
23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
24. Change the “Command Type” Dropdown to be “SQL”
25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.
    
    e.g. exec GetUsersByAreaandName Eastern, Smith
    
    If you wish to pass in a Null, then simply type “NULL” as a parameter.
    
    e.g. exec GetUsersByAreaandName Eastern, NULL
    
26. Press “OK”, and Press “Yes” to the warning box
27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!