Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query to Treat Excel
As a Relational Data Source

(continued)

Choose the Select Workbook button, launching the Select Workbook dialog.

Use this dialog to navigate to the workbook that will serve as your data source. Here, OEDATA.xls contains my Order Entry Data. Select the workbook from the list.

Choose OK to accept your Database Name selection. In the ODBC Microsoft Excel Setup dialog, choose OK to return to the Create New Data Source dialog. This dialog now shows the path to your Excel workbook that acts as your database.

Choose OK to return to the Choose Data Source dialog. Note that the Data Source you just created is already selected in the list.

Make sure the check box at the bottom of the dialog, "Use the Query Wizard to create/edit queries," is NOT checked. The Query Wizard can help if you are doing very simple queries, but I want to show you more powerful features of the program. You can experiment with the Query Wizard later, if you like.

Now that you've defined an Excel workbook as a relational database, you can use it in queries.

Create a Query

The Choose Data Source dialog now includes the data source (MSQuery--Excel) that we've defined for the OEDATA.xls workbook. Choose OK to use this data source. This data source will appear each time you access the Choose Data Source dialog.

After you choose OK, Excel displays both the full-screen Microsoft Query application window and the Add Tables dialog. You will use these tools to specify what data you want returned, either by pointing and clicking, or by pasting an SQL statement into the SQL window. For this example, we'll use the point and click method.

 previous 1 2 3 4 5 next