Excel User DSN Error

In a previous post, I described how to set up an ODBC data source on your machine for use in an Excel workbook (Import Data into Excel using a SQL Query). Now if you send this workbook to your colleagues and they try to refresh the data, they are likely to get the following error message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

This is because your colleagues also need to set up the data source on their machine (step 1 of Import Data into Excel using a SQL Query). They will also need to edit the connection string in the Excel workbook. To do this, open the workbook and go to “Data”, and then “Connections”.

Select the connection in the dialog which pops up and then click on “Properties”.

In the “Properties” dialog, click on the “Definition Tab”. The connection string, which specifies information about the data source and how to connect to it, can now be seen. Your colleague will need to edit the user ID (UID) and workstation ID (WSID). They will also need to edit the DSN and Description fields if they entered a different name and description when connecting to the data source. Your colleague can find their WSID, or computer name, under System on the Control Panel.

Import Data into Excel using a SQL Query

(Excel 2010, Windows 7)
Follow the steps below if you want to import data into Excel from a SQL Server database using a SQL query.
Step 1: Create the data source on your machine.
Type “ODBC” into the Start Menu search and then click on “Data Sources (ODBC)”.

The ODBC Data Source Administrator should open.

Click on “Add” and then enter a Name and Description for the SQL Server which you wish to connect to.

When you have successfully created your data source, you should see it listed as a user DSN (Data Source Name).

Step 2: Connect to the data source from within Excel.
Open Excel. Click on the “Data” tab, then “From Other Sources” and finally “From Microsoft Query”.

Select the data source you created in step 1, un-tick “Use the Query Wizard to create/edit queries” and then click OK.

Close the “Add Tables” dialog which pops up, and then click on the “SQL” button in the “Microsoft Query” dialog.

Paste your SQL into the “SQL” dialog which pops up.

Click OK and then click OK again when asked “SQL Query can’t be represented graphically. Continue anyway?” Finally, click on the “Return Data” button to return the data to an Excel sheet.