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

    image

  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:

    image

  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:

    image

  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:

    image

  20. Click “Properties…”
  21. The “Connection Properties” window will be shown:

    image

  22. Click the “Definition” tab, which will show:

    image

  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!

By |2017-07-24T08:33:20+01:00March 1st, 2011|Data, Excel|0 Comments

About the Author:

Leave A Comment