SmittyPro - Witticisms, Help, Tutorials and More
Jul
28

How to Import Access Data to Excel: the Easy Way…

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Many people (especially in Finance) use Excel and Access almost interchangeably, feeding data between both applications. But if you’ve never done it, then how do you? There are several ways to import data from Access to Excel, and you don’t even need to have Access open. You can import Tables, as well as most queries (Crosstab queries aren’t supported), so it’s up to you to determine which you want to import. In general, since you use Access to store large amounts of data in tables, then use its fantastic querying ability to pare that data down into manageable chunks, you’ll find yourself importing query results most of the time.

In this article we’ll discuss the easiest way to import Access data into Excel whether from Tables or Queries. To get started go to Excel’s Data menu–>Get External Data–>From Access (this was a new option with Excel 2007 and the Ribbon Interface), although if you’re familiar with it, the old MSQuery option is still there, which we’ll discuss in another article.

Get External Data

From there, select the database that you want to use as your import source. Excel will then display a list of the available Tables and Queries that can be imported:

Select Table/Query Dialog

In this case we’ll import the Customers table. Once you’ve selected the Table/Query that you want to import, Excel will ask you how you want to import it. For this example we’re just going to import the Access data as a table, although you can see that Excel gives you several options for importing the data. You should note here that while you can’t currently import Crosstab queries from Access, you can import the data that supports the Crosstab as a Pivot Table report (after all, a Pivot Table is essentially Excel’s version of an Access Crosstab query). Another option there is to create a Select query using your Crosstab query as the data source, then import the resulting Select query.

Sidenote: If you’re using Excel 2013 you’ll see a check box at the bottom to “Add this data to the Data Model”. This is an exciting new feature that the Excel team developed that allows you to create relationships between tables (a lot like you’d do in Access when building queries). If you’re interested, then make sure to check out this article from Diego Oppenheimer, Program Manager for the Excel development team at Microsoft: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx.

Import Data Dialog

Properties – While you can certainly get detailed with Import Properties, for this article I just wanted to point out a feature that’s handy if you have a data set that refreshes at regular intervals: you can tell Excel to automatically refresh your Access data whenever you want.

Connection Properties

Skipping past the Data Model and Properties, if you hit OK your table data will be immediately returned to Excel:

Imported Data in Table Format



If you’re at all familiar with Excel’s Tables, then you’ll recognize this format, which you can modify in the Table Tools Design tab, which will become active when you select any range in the table.
If you haven’t taken advantage of the Refresh options in the Properties dialog, you can refresh on demand from the Table Tools Design tab or the Data tab.

Table Tools Refresh Option

Data Tab Refresh Options

Note: The database examples used in this article were from Microsoft’s Northwind database for Access 2010, which you can find under File–>New–>Samples.

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Leave a Comment - Here's your chance to speak.(eMail will not be published)

Tags:
Separate individual tags by commas

Before you post, please prove you are sentient.

What is 3 * 6?

Microsoft MVP

 

Join In

Tag Cloud

Great Links

Archives

SmittyPro.com

Welcome to SmittyPro.com, your one-stop for solutions concerning anything from beginner to extremely advanced Microsoft Excel issues and programming. Topics covered include Excel, Access, VBA, and (every now and then) some amazing observations or "Smitticisms" that might dumbfound you and send ripples of excitement or intrigue around the world.

Categories

Meta

RSS Top Stories from CNN

Vistor Counter for SmittyPro's Blog