Exporting Data from an Access Table to an Excel Spreadsheet in Microsoft Access

IsáBel .
A free video tutorial from IsáBel .
Microsoft Certified IT Professional. Trainer of 21 Years
4.4 instructor rating • 14 courses • 28,781 students

Lecture description

Temporarily or permanently move data from your Access database into an Excel workbook.

Learn more from the full course

Microsoft Access: Beyond the Basics of Access Bundle

Don't Miss This Info Packed Three-Course Bundle: (1) Customizing Your Database (2) Automate w/Macros (3) SQL Scripting

08:53:31 of on-demand video • Updated May 2020

  • This bundle combines 3 courses to help you conquer Access in weeks - not months or years (because who's got time for that?)
  • Learn TIME-SAVING Database Tips, AUTOMATE Your Database with MACROS, and SCRIPT SQL all in one course!
  • ----------
  • ----------
  • SET Field Validations. Make sure users are entering the correct data.
  • IMPORT and EXPORT Data Using Microsoft Excel, an application you probably use daily.
  • CREATE Cascading Combo Boxes. Base the items in a combo box off what was selected in another combo box.
  • CONDITIONALLY FORMAT Forms & Reports to make your data easier to understand.
  • CREATE Parameter & Action Queries. Go beyond standard Select Queries
  • CUSTOMIZE the Access Environment Ribbon, Navigation Pane & Startup
  • UNDERSTAND Database Maintenance Tools to keep your data intact.
  • USE the Macro Builder Window to automate your database
  • CREATE Data Macros for Table Automation
  • CREATE Macros for Forms & Reports
  • CREATE AutoExec Macros to run Macros when starting your database
  • SCRIPT SQL to Query Your Tables
  • CREATE Database Tables with SQL
  • CREATE Table Joins with SQL
  • CREATE Action Queries with SQL
  • ... and so much more!
English [Auto] A very common question that I get is how do I take information from Access and export that into Excel or vice versa. How do I take information into Excel and imported into Access. Well first I'll talk about how to take information that's in access inside of a table and export it out to AXO. So the first thing I'll do is open up my tables here and my navigation and maybe I want to take my list of customers and send that over to excel. So I'll go ahead and double click on customers so you can see what this table looks like. We've got many different columns here. The company that the customer works for their first name last name email job title and so on. And I want to take all this information and copy it over to excel. So that's done by going up to the External Data tab in the ribbon and it's broken down into two different groups. You've got import and link and export and you notice that in the export group the second button here on the left hand side is ex-SO. So I go ahead click on Excel Access has set the destination for me which has my documents folder. And since I'm taking a look at the customers table it's naming the Excel file customers. Now you can change the location where this is going to be saved by hitting the browse key or typing the path in the file name. Next I have the file format and it's going to be saved as an Excel workbook. Now this is going to be saved as a 2007 or later file format. But if you want to save something earlier like Excel 2003 you can do that. Or a binary file. All right I'll go ahead and leave it as an Excel workbook. Now I have some options here on how I want to export it. Do I want to export it with my formatting and layout. Now it doesn't necessarily keep all of your formatting but it's going to try to preserve most of the formatting. You can read that right there. And you notice that I have two other options right now which are great out. The first one is to open the file after I export it and then the next one is to export only the selected records. Why I actually don't have anything selected right now so it's still going to stay greyed out. Let me go ahead and click on export data with formatting and lay out and you'll notice that just open the destination of Prowse has been enabled export only selected records is still greyed out. Let me show you this. I'll go ahead click on cancel and I will select maybe a couple of columns click on my Excel export one more time click on export data with formatting and I can see that all three of them are now enabled. All right now I want to export the entire table so I'm not going to check that last one but I will open the destination file after the export is complete. So it's a really simple window. You just make sure the file name and the destination is correct. If not go ahead and browse out you change the file format if you need to be something before Excel 2007. You choose to export it with the formatting in the layout and then if you want you can open the destination file after it's export it and if you have a couple of record selected you can export only those records. That's it. I'll click on OK. It opens up my Excel minimize it down a little bit so you can see it better. There we go. So notice here that it exported out all of my records not just the ones that I had selected and I've got all my information there so it's perfect. It worked. Now one thing that I do want to point out is if you take a look at columns in and are that's my zip code and my attachments. Those were numbers but notice that they came in here as text and you can tell that it's text two ways. One if it were a number it would be a line to the right. And here both of these columns are aligned to the left. So that's text. Also I get a little green indicator on the top left hand corner of each of these cells. And if you click on it you get a little warning and here you can convert it to a number. All right. So I don't want to convert just one so I want to convert the entire column. So what I'll do is click on my first cell here in the column and then I'll control shift down arrow to get my entire column of information. I'll click on my little warning icon one more time and choose convert to number and did it. Now my zip codes are numbers. I can do the same thing with the R column. I can actually highlight the entire column if I think I'm going to be adding in more records. And again click on my indicator and choose to convert I right. So there we go. Super easy to export this out to Excel. Now if you think that you're going to be doing this export over it over and over again you may want to save the steps that we just did into access and you can do that. Let me go ahead just close this Excel document. And help us save the changes. Now back here inside of Access it's asking if I want to save those steps. Well I'll go ahead and say Yes say the export steps. It's asking me to give it a name export customers table and a description maybe something like my name especially if I am sharing this database with other people I'll put my name the date that this export was created and why on exporting. And then lastly you see that you can also create a task inside of outlook. So if this is something that I do quite often I can click on Create outlook task and then I can turn this into a recurring task in my Outlook or go ahead and check that though and click on Save export. All right. So next time I need to export the customers table out to Excel and set up with non-access and then going through that short wizard what I could do is go to saved exports and there it is right there. I would just make sure it's highlighted because of course there might be five or six or 10 or 20 different experts in this window. Make sure it's highlighted come down to the bottom left hand side click on to run or say yes to overwrite that existing table and there we are a brand new fresh copy of the customer's table inside of Excel and save to my documents folder.