While Access 2007 is an accessible database application, sometimes other programs are better for collecting, analyzing, and manipulating data. Excel, a kind of two-dimensional flat database, can do things that Access cannot including producing certain kinds of reports, making calculations, and creating colorful graphs for use in home, business, and educational applications.
Access 2007 gives you the ability to export data into other file formats for use with the file format’s respective application. One such format and application is Excel, another program in the Microsoft Office suite of productivity software. Read on to learn how to export data from Access into other file formats including Excel.
Some Caveats before you Export Data from Access
Just like translating a sentence from one language to another, there are bound to be some inconsistencies and problems with exporting data from Access 2007 to another application’s file format. Logically, file formats among programs are different. If they weren’t, there would be no reason to export data from one application to another. While Access does its best to export data cleanly, especially to other applications within the Microsoft Office suite, there are some steps you can take to make certain that the data exports in the way you want and need it to.
Access allows you to specify data types that can make adding, removing, and manipulating data within a table possible and easy. Special data types such as memo and auto-numbering are likely to give you problems during an export to Excel. Similarly, OLE data types will likely give you problems because, for example, the file type to which you export the data (such as Excel) may not know what to do with a picture embedded within an Access 2007 table cell.
Your best bet is to save and backup all of your data in your Access database before attempting an export of its data. In addition, you may have to try several times before you export the data in the way you want and need. Don’t be surprised if you have to make changes to your data before you get the results you want. For simple data, such as the kind of data you would expect to find in an Excel spreadsheet, the export should go as planned without any nasty surprises.
Exporting Table Data from Access 2007 to Excel
Although you can export data from Access into a variety of file formats, Excel is usually the most prevalent file format to which people export database data. The steps outlined below could be used to export data into any of the supported file formats. For now, let’s take a look at how to export Access data to an Excel file.
Open up your data that contains the data you wish to export to Excel. Then click on the table that contains the data you specifically want to export. Here is where you would check to see if there are data in the table such as auto-numbering, memo, or OLE data that will give you problems during the export. You could try to preempt the problems by dealing with this data now or you could do the export, see what you get, make the necessary changes, and complete another export attempt.
Exporting Table Data from Access 2007 to Excel
Read on to learn how to export Access 2007 to Excel.
Click on the EXTERNAL DATA tab on the Access Ribbon and then click on the EXCEL button under the EXPORT section of the Ribbon (see Figure 1). The EXPORT EXCEL SPREADSHEET window opens. Click the BROWSE button and select the location and file name you want to use for the new Excel file you are about to create.
In the next drop-down box, choose the FILE FORMAT you wish the Excel file to have. Your choices include an Excel 2007 Workbook, an Excel 97/2003 Workbook, an Excel 95 Workbook, and an Excel Binary Workbook. The choice you make depends on your particular needs and the version of Excel you or someone else will use to do whatever it is you want to do with the data in Excel.
Below that drop-down box, you have several more option from which to choose. It is beyond the scope of this article to discuss every option but suffice it to say that the options should not provide too much difficulty.
For example, one option gives you the ability to preserve formatting and layout while another offers you the ability to export only certain records. The choices you make here are determined by what data you need, what format you need them in, and what you will be doing with the data after the export is complete.
After choosing your options, click on OK. Access 2007 then gives you the option of saving your export steps. If you export data often using the same options each time it may be worth your while to save the steps and avoid having to repeat them each time you export data to Excel (or another file format).
Then, click on CLOSE and the export is complete. Go to where you told Access to save your new Excel file and open it up. Here you will see whether the data export from Access was successful and gave you what you expected. If not, make note of each change you would like to make and make the necessary adjustments in your Access database. Then, complete another export and see if you get what you wanted. Likely, it will take only one try for you to get the Excel file the way you need. However, extremely large database and database with lots of different types of data are more likely to cause problems.
Exporting data from an Access 2007 table is a simple matter of using Access’ built-in Export Wizard. Using this wizard, you can export data from Access to Excel. Sometimes, data in an Access database is not compatible with Excel necessitating you to make some changes to the database and try another export attempt. Keep in mind that you need not export all of the data in a table. You can create a special query or report in Access so you only have to export the data you want. Luckily, Access and Excel were designed to work well together so exporting to Excel usually goes smoothly.