Using MS Query to get data from Excel

One of the most powerful data acquisition and data analysis tools in Excel is MS Query.

Excel 97-2003
[tt]
Data > Get External Data > From Microsoft Query...
[/tt]
Excel 2007[sup]+[/sup]
[tt]
Data > Get External Data > From Other Sources > New Database Query...
[/tt]
At this point, the MS Query Wizard displays the Choose Data Source window for Databases, Queries, OLAP Cubes.

In the Databases tab, look for
[tt]
...
Excel Files*
MS Access Database*
...
[/tt]
Did you know that an Excel workbook can be queried, data retrieved, data updates/inserted without the Workbook being open?

CAVEAT: The workbook you want to query, must have Sheets formatted as Tables -- One or more Sheets with One row of Headings, rows of data under each heading and no other data on the sheet. faq68-5184


So if you select Excel Files* in the Databases tab you get the...

Select Workbook window. Select a workbook and you get the...

Query Wizard - Choose Columns: and you see a window that lists available tables (Sheets) and columns if you open any table. [red]If you do not see your SHEET NAMES in the Add Tables Window, click the OPTIONS Button and check all boxes[/red]

Choose a Table/column(s) for you query.

From here I usually just get right to the Query Editor: [Next],[Next],[Next], View data or edit query in Microsoft Query, [Finish]

Now you're in the MS Query Editor/QBE Grid. If you're familiar with database programs, the Query editor has limited functionality, but you can still do quite a bit, especially using the SQL button to edit native SQL code.

After you've added your criteria, added/joined tables

File > Return data to Microsoft Excel.

What you have created is a QueryTable object in your sheet. In the Query Wizard there was a place to SAVE the query. The only reason that I know of to save the query, is if you wanted to use the same query in another workbook or worksheet. I usually do not save my queries. As long as the QueryTable object is in your Sheet, it's there and can be refreshed

Data > Refresh Data.

Happy Excel Querying!