Home

Microsoft Access and Microsoft Excel
 

Exporting to a Microsoft Excel Spreadsheet

As a spreadsheet application with various analysis tools, Microsoft Excel provides some features that are not available in Microsoft Access or some results can be difficult to get in the database. Therefore, it would not be unusual that you want to use the data of a table in a spreadsheet. You have various options.

Probably the easiest way to transfer data from Microsoft Access to a spreadsheet involves copying and pasting. To start, in Microsoft Access, you can open a table in Datasheet View then select one, a few, or all records. After selecting the record(s), you can copy them, open Microsoft Excel, click the cell that would host the top-left value, and paste.

If you use the copy and paste technique, you would have to open the table first. Another technique consists of exporting, and you can do it without first opening the table:

  • Before exporting a table, in the Navigation Pane, you can first click the table. Then, on the Ribbon, click External Data. In the Export section, click the Excel button Excel
  • In the Navigation Pane, right-click the desired table, position the mouse on Export, and click Excel

This would open the Export - Excel Spreadsheet dialog box with the path where the file will be saved. The default folder is the My Documents:

The dialog box provides various options. For example, if you have Microsoft Office Excel 2007, you can accept the format with .xlsx extension. If you have a previous version, click the arrow of the File Format combo box and select the version. After making the selections, you can click OK. When the exporting is over, a message box would let you know.

Importing a Microsoft Excel Spreadsheet

If you had worked on a spreadsheet to create a list of items and you want to convert that list into a table useful in a database, you can. Microsoft Access allows you to import a spreadsheet from Microsoft Excel, Corel Paradox, Lotus 1-2-3, or one of many other spreadsheet applications on the market. Before importing the spreadsheet, you should prepare it and you have many options. When creating the spreadsheet, you may have created a list anywhere on the document, without a title, and only the list of values. Here is an example:

When creating the spreadsheet, you may have created a list anywhere on the document, without a title, and only the list of values. In this case, the spreadsheet is ready. When you import such a spreadsheet, Microsoft Access would be able to figure out where the list starts and where it ends. Microsoft Access can even identify the column names and their records (where the records start and where they end)

In this case, the spreadsheet is ready. When you import such a spreadsheet, Microsoft Access would be able to figure out where the list starts and where it ends. Microsoft Access can even identify the column names and their records (where the records start and where they end). The database engine can also identify the data type of each column, based on the values used in their fields.

On the other hand, you may have created a spreadsheet that includes sections other than the list you want to use in your database. Here is an example:

You may have created a spreadsheet that includes sections other than the list you want to use in your database

If you try to directly import such a spreadsheet, the database engine may get confused. It would not know where the actual list starts and where it ends. If you insist, you can still import the spreadsheet but you would get unpredictable results. If you create a spreadsheet in Microsoft Excel, Corel Paradox, or Lotus 1-2-3, and if the spreadsheet contains a mix of the desired list and other items, you can create a "name" for the list. To create a name in Microsoft Excel, select the list:

If you create a spreadsheet in Microsoft Excel, Corel Paradox, or Lotus 1-2-3, and if the spreadsheet contains a mix of the desired list and other items, you can create a name.

Then, on the Ribbon, click Formulas. In the Defined Names section, click the Define Name... button. In the New Name dialog box, specify the name and click OK. To create a name in the other applications, check their documentation.

After creating, preparing and saving the spreadsheet, you can import it. To do this in Microsoft Access, start a database:

  • On the Ribbon, click External Data. In the Import section, click the Excel button Excel
  • If you have created a table that will receive the data, in the Navigation Pane, right-click that table, position the mouse on Import, and click Excel

This would start the Get External Data - Excel Spreadsheet wizard. You would then have to specify the file that holds the spreadsheet, and click OK. In the second page of the wizard, you would be asked to identify the section (sheet) where the table exists. You would also have the opportunity to provide some details about the data being imported.

Instead of manually importing a spreadsheet, you can select the values in a spreadsheet, copy them, and paste them in a table in Microsoft Access.

 
Home Copyright © 2008-2016, FunctionX, Inc.