Querying External Data in Excel

What is External Data
Create a query in the UI
The External Data Toolbar
Edit a query in the UI
Create a query with VBA
Delete a query with VBA
Change the SQL with VBA
Change the database location

What is External Data

Excel provides the ability to bring data that is "external" to the current workbook into your workbook.  The examples shown here demonstrate how to include data stored in an Access database into a workbook and manipulate that data in various ways.  You can, however, bring data from any data source that has ODBC capabilities.  For example, many accounting software packages provide ODBC files with which you can query your accounting data into Excel. 

Create a Query Through the User-Interface

Under the Data menu, there is a "Get External Data" option.  This is called "Import External Data" in versions starting with Excel 2002.

To start the process of querying an external data source, choose the New Database Query option.  The Choose Data Source dialog appears and is shown below.  Also note that Microsoft Query has started and shows in your Taskbar.  Microsoft Query is the program that Excel uses to query and return the external data.

Note that MS Access Database has been selected.  As these examples will be using the Northwind Sample database that ships with Access, that is the option we want.  Also note there is a checkbox at the bottom of the dialog that allows you to use the Query Wizard.  If you are particularly adept at writing SQL or you are creating a complex query, you may want to uncheck this box and create the query manually in MS Query.  I generally use the wizard even if I know that I will be modifying the query in MS Query later.  With MS Access Database selected, click on the OK button.  You will be presented with a standard file navigation dialog that allows you to select the database you would like to query.

Navigate to Northwind.mdb and click OK.

This starts the query wizard and shows the first page, Choose Columns.  The left listbox shows all the tables and queries in the database.  Unfortunately, it doesn't show which are tables and which are queries.  I advocate a naming convention when using Access where tables start with Tbl and queries start with Qry, but that's just me.

For this example, choose the Customers table by clicking on the plus sign next to it.  This will expand that table to show the available fields.  To include a field in your query, select it and then click the greater-than-sign button (>).  The selected field will be shown in the right listbox.  Double clicking the field name works also.

Select the CustomerID, Company Name and City.  Your dialog box should look like this.

If it does, click Next to show the Filter Data dialog.

The Filter Data dialog allows you to set up criteria for your query.  If you know SQL, you're writing the WHERE clause.  Setting up criteria is simply limiting the records returned by your query.  Let's limit this query to only those records whose City is equal to Berlin.  To do this, select City in the left hand listbox, select equals in the first combobox and Berlin in the second.  Like this:

You can set criteria for any or all of the fields and you can set multiple criteria for the same fields.  If you use And's and Or's, you may not get the results you want.  Generally, the more complicated your criteria, the better off you are doing it manually in MSQuery.  If your screen looks as above, click Next to show the Sort Order dialog.

The Sort Order dialog is pretty straight forward.  You can sort by up to three fields in ascending or descending order.  If you don't choose any field, the sort order will be whatever order the underlying table or query has.  Choose to sort by CompanyName in Descending order so that your screen looks like this:

Click Next to show the Finish dialog.

As its name suggests, the Finish dialog is the last step in the process.  You'll notice that you have three radio button options.  You can return the results of the query to Excel, edit the query further in MSQuery, or create an OLAP cube.  OLAP cubes are beyond the scope of this article, so we'll focus on the first two.  Here's what your dialog should look like:

Click Finish to return the data to Excel.  Excel will again be in focus and will show the Returning External Data to Microsoft Excel dialog box, shown here.

The default is to return the data to the existing (active) worksheet at whichever cell was active when you started the process.  The "marching ants" are circling the cell shown in the refedit control (G7, in this case).  Change it to A1, and click OK.  You can change the query table properties via the Properties button, but it's just as easy to change them once the data is in Excel.  Now your spreadsheet will look like this:

That's right, one whole record.  Woo hoo!  That tells us that the Customers table in the Northwind database has one record whose City is Berlin.




                    Contact Me            Visit Daily Dose of Excel - an Excel weblog