Rule of Thumb

You know that the data is out there and you want to bring it into your homely Microsoft Excel. Here’s how you do it.

1. If you don’t have a data source pointing to your database yet, create one.
2. In Microsoft Excel choose Data, Import External Data and then New Database Query.

1. Select your data source and click OK.
2. For interesting queries un-check Use the Query Wizard to create/edit queries.
3. Click OK and Microsoft Query appears.
4. Choose tables, fields, joins, filters, groups and sort orders as you like or write your SQL SELECT directly by clicking on SQL button (View, then SQL).
5. When you’re done, click Return Data button on the toolbar (File, then Return Data to Microsoft Office Excel) and you’re back in Excel with selected data.

1. Just choose where exactly do you want that data. You can also choose Create a PivotTable report to have Microsoft Excel create a PivotTable report right away or click Properties, to tweak some settings on how this data is imported or Edit Query, to return to Microsoft Query.

Now you can right click anywhere on the table and either Refresh the data, Edit Query or change the External Data Range Properties.

You need to set up ODBC data source to do any serious number crunching, so here it is.

You can set up data source either directly from Microsoft Excel or through ODBC Data Source Administrator. Eventually you will reach the same dialog boxes, so I’ll explain the later approach. It is more intuitive to administrate data sources from there.

To open the ODBC Data Source Administrator in Windows XP Professional:

1. On the Start menu, click Control Panel.
2. In Control Panel (Category View), click Performance and Maintenance, and then click Administrative Tools, or in Control Panel (Classic View), click Administrative Tools.
3. In Administrative Tools, click Data Sources (ODBC).

1. You can either create User DSN, System DSN or File DSN. System DSN can be used by anyone who has access to the system. System DSN info is stored in the registry. User DSN can only be used by you. It is also stored in the registry. File DSN is stored in a text file with .dsn extension and can be copied around. Choose appropriate pane, depending on your requirements or go with User DSN if you’re not sure.
2. Select Microsoft Access Database and click Add.

1. Select Microsoft Access Driver (*.mdb) and click Finish.
2. Click Select and browse for your Microsoft Access Database (*.mdb) file.
3. Name the newly created data source in Data Source Name

1. Click OK.

1. Newly created data source is now available in the ODBC Data Source Administrator.