How to Import Access Data to Excel Using the “Get External Data – From Access” Option
July 2013
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.
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:
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).
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.
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.
Skipping past the Data Model and Properties, if you hit OK your table data will be immediately returned to Excel:
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.
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.
Great article. This helped me shorten my coding time. I do have an issue. Not all the Queries are showing. I would understand why and Update, insert or Delete would not show but I’m not getting the majority of my Read queries. Some are selecting from one table and some are not. The ones not showing up are with Parameter entries. How can I get these to prompt for the parameters? I’m using Office 2013.
Thanks!
Hi,
I tried the same procedure, but could not get success. I am using MS Access 2013 and MS Excel 2013. Once I select the access file from the dialog box, nothing happens. The control is back to the same excel sheet. Please help.
* Hello Niteen. Thank you for your interest in our site and for your taking the time to post this comment.
** We own and operate one of the largest free Microsoft Excel help forums on the web. You can post any and all of your questions there by using the link below. The team of Microsoft experts will assist you and then you should be good to go.
http://www.vbaexpress.com/forum/forumdisplay.php?17-Excel-Help
Thank you.