It is easy to pull data from a MS SQL table or view into excel, the UI is very straight forward for that. But that is not the case when you want a custom sql query to be the one pulling data from the database.
To do this in Excel 2010, you need to first set up the connection to the database and then set up the query:
Setting up the connection
This is where you set the server location, login, AND the database you want to query:
Leave the default table empty:
Double click your newly created data source
Building the Query
The screenshot below pops up automatically when you double click the data source (see screenshot above). We don’t want to add any tables:
We want to use our hand written SQL:
Enter your SQL:
Chances are you’ll run into this, hit ok
Your query has been built, hit the exit button:
Tell Excel where to put the data from your query:
You’re done!
You can now right click on any cell that has data from your query and hit Refresh when you want Excel to pull fresh data from the database:
Pedro Liska 