Moving Access Data to Microsoft Excel
Microsoft Access has some built-in ways to move data from Access to other Office Applications and it comes in the form of a simple little menu option that people too often forget about.
Open up any query in a database (for the purposes of not crashing your machine – try to make it a standard select query and a small one at that, or put on some criteria so you don’t have a gazillion records ready to be shifted about). While looking at the query in datasheet view, click on “Tools”, then “Office Links”, and then “Analyze It with Microsoft Excel” (see Figure 1).
All of a sudden, just mere moments later, bingo! You’ve got your data in a nice and comfy Excel Workbook ready to analyze. This is probably the easiest Microsoft Access export you'll ever have the pleasure of doing!
Now if you opt to move the data into Microsoft Word with the “Publish It with Microsoft Word” option, you may encounter some formatting issues if you have a large number of columns in the query or some of your data happens to be larger. While this isn’t a big issue if you don’t mind doing a little Word Table formatting – many times that’s just not a preferred way to spend an afternoon.
The “Merge It with Microsoft Word” option you see here in my screenshot of Access XP simply takes your data and dumps it into an existing Mail Merge Main Document or allows you to create one. If you have a form letter that you’ll be addressing to a few hundred people you have stored in a database, this is a great option to get all of the letters made with every piece of specific information in place (ie: Each letter will be personalized). Mail Merges open up an entirely different world of possibilities that we won’t cover right now – just know that it’s there and ready to be used!
Really, this “easy” method of moving data out of Access should cover about 90% of everything you’ll ever need to do. Most often, it’s about getting data from point A to point B and since Access does it for you easily, there’s pretty much no real drawback unless you need a little customization – or something a little different.
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: