Internet Sellout

Demand Unearned Rewards

Excel ODBC DSN-less Connection

There are times when you want to create a data connection in Excel that doesn't require special drivers or a file based connection that must be present on the users computer.

I always have trouble remembering the path to this. The good thing about this method is you can use it to call stored procedures with parameters that filled from cell values.

This post sponsered by http://rollingboom.com/

Step 1. Data Tab in the Ribbon, From Other Sources.

Step 2. Choose what appears to be the least likely option, "From Microsoft Query".

Step 3. Create a "New Data Source".

Step 4. Fill out the create a "New Data Source" form.

 

Step 5. Now that your datasource is selected you can choose it.

Step 6. Just close this.

 

Step 7. Click the SQL button

Step 8. Enter the SQL statement or stored procedure name.

Step 9. Click the return SQL button.

Step 10. Pick a range.

Step 11. Maybe you need to make it remember the password. This step is optional.

Ok, now you have a worksheet with a nice table with data. When you press refresh in the data tab you get a requery.

In Step 11 you can add parameters like:

{CALL usp_MyStoredProcedure (?,?,?,?)}

Each one of those question marks is a parameter and the parameters button should activate when you have them in your statement.

 

Comments are closed