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
Popularity: 4% [?]
Recent Comments