Saturday, May 12, 2007

Create SQL Server database query with MS Excel

1. Start a new XL workbook
2. Click Data ~ Import External Data ~ New Database Query
3. At the "Choose Data Source" dialog, either select an existing DSN or create a new one, then click OK.
4. At the "SQL Server Login" dialog, enter your credentials and click OK
5. At the "Query Wizard" dialog, click Cancel
6. At the "Do you want to continue editing this query in Microsoft Query?" click Yes.
7. At the "Add Tables" dialog, click Close
8. On the MS Query toolbar, click the SQL button
9. Enter your SQL statement in the "SQL" box and click OK
10. If your SQL statement is a stored procedure, it will say "SQL Query can't be represented graphically. Continue anyway?" - click OK
11. You should now see your data displayed in MS Query
12. Click the Return Data button (looks like an exit door) and MS Query will close
13. Now you're back in XL. At the "Import Data" dialog, select the location for the top left corner of where you want the data. It defaults to cell A1 which is usually where you do want it to start; click OK
14. Excel will now retrieve the data and place it starting from the chosen location

No comments: