Import Access Data into Excel via Power Query

Much has changed since 2018; the year Microsoft Excel went dynamic.   The way people import Access data into Excel has dramatically changed too.  These days, Power Query is how data importation is done.  There is no need to use VBA, no need to copy and paste.   Simply “RefreshAll” in Excel, Power Query will do its thing, and the Access data is instantly imported.  You will see how easy it is to Import Access Data into Excel 365 via Power Query.

 

In the sample video, I was able to create a new connection, within seconds, and the data was instantly imported.  It took less than 30 seconds to set that up.  Importing Access data into Excel via Power Query is the way to go.  Integration and automation at its finest.

 

Importing Access Data into Microsoft Excel has Never Been Easier

No need to create links, no need to write vba, no need to copy-paste; just use Power Query.  In addition to importing your Access data, Power Query will also allow you to “Transform” your data.

Excel Expert’s Note:  If you want to filter the data coming from Access into Excel, Power Query was built to do that.  Only import the data that you need.

 

 

 

---

 

 

 

 

 

 

 

 

 

Conclusion:  Import Access Data into Excel 365 via Power Query to save time and money

You program the file one time.  Then, each time that you use the file, you save time, which is money.  Power Query is one of the best ways to automate Microsoft Excel.

 

 

---

 

If you need help with anything Excel, Access or Power Query, please contact us.

877-392-3539

 

 

---

 

That was then (Below- older post), this is now (above - new content)

 

How to Import Access Data to Excel Using the “Get External Data – From Access” Option

 

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 Dataform 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.

 

Updated 9/24/2024