Creating an MS Excel Spreadsheet from MS Access Database Data

In many cases, it is desirable or necessary to export data from your MS Access Database to a MS Excel Spreadsheet format. An example would be using MS Word's Mail Merge Wizard. MS Word cannot take data directly from an Access database, however it can import data from an Excel Spreadsheet.

To get the appropriate data, the data you want on the Excel spreadsheet, from a table in Access, the first step is to create a query. A query is nothing more than a command given to the database requesting the information you are asking for. For the purpose of this article, we will be using a database that stores information on personal contacts. We will create an Excel spreadsheet that could be used for creating address labels using MS Word's Mail Merge Wizard.

In this database, the table "contacts" is where we are going to query our data. The following picture shows the Datasheet View of the table Contacts.

To create a query, open the Database Window, shown below. Click on the queries button. This will show all the queries in the database and two other Create Query selections in the window. Along the top tool bar there are also three other choices; Open, Design and New. Click the New button.

You will now be asked a series of questions. The first box to appear will give you five choices. Highlight the top choice, Design View and click OK. Two boxes will now open. The first one is the query; the second one is asking which table(s) you wish to use in the query. Where it is possible to use more than one table in a query, for this example we will only select the Contacts table. Highlight Contacts and click the Add button, then click the Close button. This will leave the query window open with a small window representing the table in the upper portion of the query window.

In the small window that represents the table Contacts, you will see that each column of data is represented. The lower half of the query window represents the data that the query will "show". Double click on each of the data points that you will ultimately export to your Excel spreadsheet and to your Mail Merge Wizard. Your query should now look like this:

To test the query, press the "!" button on the tool bar. The data from the table will be shown to you in datasheet view. If the data is correct, this is almost what your spreadsheet will look like. Save the query as "qry_addbook".

The next step is to build a macro. On your database window, select Macros. Click the New button on the tool bar. This will open the new macro in Design View. Click on the first Action Item drop down box. Select OutputTo. The arguments will appear in the bottom half of the form. Select the following Action Arguments:

Object Type: Query
Object Name: qry_addbook
Output Format: Microsoft Excel (*.xls)
Output File: Leave Blank
Auto Start: Yes
Template File: Leave Blank
Encoding: Leave Blank

When this macro is run, Access will create an Excel file based on the query in the argument. By leaving the Output File argument blank, Access will prompt the user to name the new Excel file. By selecting Auto Start to Yes, Access will open Excel and the new spreadsheet. Save your macro as "mcro_addbook". Test your macro by clicking the "!" button on the tool bar. You should be prompted to name the new file, and Excel should open with the new file opened.

For ease of use, it is desirable to create an easy way to run this new macro. A button on a database's Main Form or Switchboard is a common way to accomplish this. Close the macro window and open the Main Form or Switchboard of the database.

When the form is open, click on the Design View button. The Design View button is the furthest left button on the toolbar shown above. This will enable you to make changes to the current form.

This changes the look of the form a little. It is now editable. An additional toolbar should have also appeared. On this new toolbar, find the Command Button button. It looks like a command button. Select the Command Button by clicking on it, and then click your form in the approximate location you would like the button to be placed. You will be able to adjust its exact location later. When you clicked on your form a new window, the Command Button Wizard, should open. This wizard will allow you to easily set up your new command button.

Select Miscellaneous and Run Macro, click Next. Select the macro we created, "mcro_addbook", and click Next. Choose the radio button Text, and insert the text you would like the button to read, i.e. "Address Book to Excel". After clicking Next, you can change the command button name, but it is not necessary. Click Finish.

Move the new command button by clicking and dragging it. Then click the Form button on the toolbar. It is in the same place the Design View button was. Your new button should be active on your form. Save your work and click the new command button to test the functionality of your changes.- Lloyd E Duncan