Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query to Treat Excel
As a Relational Data Source

After you've added all the fields you want, click on the Query Now button, shown here. The data will be returned in the data grid, as shown in top few rows of this figure.

Note that the data grid isn't limited to 65,536 rows. If you suspect the dataset you've returned is larger, you can check this by clicking on the “Last Record” button at the bottom of your window; it's the right-most button shown here. Here, for example, the query produced 140 records.

Now would be a good time to save your query. This will allow you or another person to use the same query later in a new workbook, with additional data, or both. To save the query, choose File Save As in the Microsoft Query window and then name your query anything you want. In the File Save As dialog you'll see two file formats, dqy and qry. If dqy is specified as the default, use that format. The qry file format was used in earlier versions of the tool.

At this point, you may be curious to know what the SQL statement you just generated looks like. When you click on SQL toolbar button shown here you can see the SQL statement in the SQL window. If you know SQL, you can edit the statement to add features that are not supported by the generator, but are supported by the ODBC driver you're using.

Export the Data to Excel

If the data in the grid is what you want to export to Excel, click on the Return Data button, shown here.

You'll be returned to Excel, and the Import Data window will let you decide where you want to put the data. For this example, I'll accept the defaults, and put the data in the existing worksheet in Column A, Row 1, by clicking the OK button.

You now can apply any formats, formulas, and so on, that you wish.

previous 1 2 3 4 5 next