×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs
Home > Forums > MIS/IT > Office Suites > Microsoft: Office > FAQs

Microsoft: Office FAQ

Best of Excel

Using MS Query to get data from Excel by SkipVought
faq68-5829
Posted: 21 Apr 05 (Edited 17 Jul 12)

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

Excel 97-2003

Data > Get External Data > From Microsoft Query...

Excel 2007+

Data > Get External Data > From Other Sources > New Database Query...

At this point, the MS Query Wizard displays the Choose Data Source window for Databases, Queries, OLAP Cubes.

In the Databases tab, look for

...
Excel Files*
MS Access Database*
...

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: What are LIST & TABLE PRINCIPLES for Spreadsheet Users


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. If you do not see your SHEET NAMES in the Add Tables Window, click the OPTIONS Button and check all boxes

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!

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Resources
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close