Exporting Access Data To Excel
IIHE  |  Education  |  Getting Started With Microsoft Access  |  Exporting Access Data To Excel

You can't do much analysis on a table or query inside of Access. If the table or query isn't too large, you can import it into Excel. Excel offers many functions for analyzing your data: totals, average, mean, total counts, counts for a criteria, and some statistical analysis.

Excel can hold a maximum of 65,535 records. You will need extra rows available for field headings, subtotals, totals, and whatever other analysis that you perform. Probably, a safe limit would be about 65,000 records. Also, an Excel spreadsheet can only have 256 columns. An Access table can have up to 255 fields. Again, you will probably need some extra columns for calculations and totals.

There are three ways that you can transfer data from Access to Excel:

 

Copying and Pasting Data

If you don't have too much data, you can export the data to Excel by:

• In Access, highlight the data that you want to copy to Excel. To select all the data in the table or query, press Control A or pick Select All Records from the Edit menu.
• Copy the data to the clipboard by pressing Control C or choosing Copy from the Edit menu.
• Run Excel (or switch to it if it is already running).
• Click in the cell where you want the data to start. Usually, this will be cell A1 in the upper left-hand corner.
• Paste the data by pressing Control P or choosing Paste from the Edit menu.
• All the data may not be visible in some cells. You can fix this by double clicking on the break line between the cells at the top of the spreadsheet. This works just like in Access and is covered in Using Tables.

Note that the names of the fields are always brought in with the data as the first row. If you are importing multiple sets of data into Excel, you can delete the row with the field names by right clicking on the row and then choosing Delete.

Importing Access Files into Excel

If you have a lot of data or would like to keep a permanent record of the data (so that you can refer to it later), you can export the data from Access to a file and then import the file into Excel. This method always exports all the data in the table or query.

To start the process, choose Save As/Export... from the File menu in Access:

Now, click on the To an External File or Database button and then click on the OK button:

The standard File Save As dialog comes up. Click on the triangle at the end of the line beginning with Save as type:.

From the pop-up menu, choose the version of Excel that you are running. If you are running Access 97 then you are probably running Excel 97. You can check the version by choosing About Microsoft Excel or About Microsoft Access from the Help menu.

Now you need to choose a name for the file and a location to save it. The default name will be the name of the query or table that you are working and have the extension .XLS.

Run Excel and open the file you just created:

Take's it. The first row contains the names of the fields.

Importing Data From Excel  |  Getting Started With Microsoft Access  |  Exporting Access Tables to Epi Info