How To Create a Microsoft Access Macro to Export Information to
Excel or Word:
End-users occasionally find it easier to work with Microsoft Excel
or Word than Access if they need to perform calculations or add formatting.
They may receive the data in raw format; however, once it is organized
in Microsoft Access, they may need to perform additional data analysis
in Excel or insert formatting available in Word.
Prior to exporting the information, it is best to isolate it in a
database query or a report. Create a Microsoft Access Select Query
or a report to isolate the information. By isolating the information,
it is easier to incorporate it into the macro. The macro that will
be created below will have a message box that appears prior to the
information export.
- Click on the "Macros" option in the "Objects"
menu.
- Click "New" in the upper-panel of the database window
to create a new macro.
- Click in the "Action" field.
- Click on the drop-down arrow and go to "MsgBox".
- Go to the "Action Arguments" pane by clicking in the
"Message" field or pressing "F6". To toggle
to and from the "Action Arguments" pane, use "F6".
- In the "Message" field, type the message that the end-user
needs. A suggestion is to let the end-user know where the file will
reside once it is exported.
For example, "The file will be exported to your desktop in
Excel." In order to have room to type and view the entire message,
right-click in the "Message" field and go to "Zoom".
The "Zoom" dialog box will open and this will provide
the opportunity to type the message.
The font can also be changed by clicking "Font…".
- Click "Ok" once the message has been typed.
- Click in the "Beep" field. If a beep is to sound when
the message box is displayed, click "Yes". Click "No"
if a beep is not needed.
- Click in the "Type" field.
- Click on the drop-down arrow. There are five types of messages
that can be displayed. The difference between the message types
is the icon that will be displayed with the message.
If "None" is selected, an icon will not be displayed.
Select "Critical" to display a red circle with an "x"
beside the message.
"Warning?" will display a speech balloon with a question
mark in the middle. "Warning!" will display a yellow triangle
with an exclamation point in the middle.
Select "Information" to display a speech balloon with
an "i" in the middle.
- Click in the "Title" box. Use this to give the message
a title, if desired. The title will appear in the title area of
the dialog box. If a title is not given, "Microsoft Office
Access" will be the title of the dialog box.
- Click on the second line for the next action.
- Click on the drop-down arrow to select "OutputTo".
- Click in the "Object Type" field or press "F6".
- Select "Query" to export query results. Any type of
object can be exported from a table to a module. In this example,
a query will be used.
- Click in the "Object Name" field, select the name of
the object that will be exported by clicking on the drop-down arrow.
If "Query" is selected as the "Object Type",
the "Object Name" field will display the queries in the
database.
- Click in the "Output Format" field. If the data is to
be exported to Excel, select an Excel (*.xls) format. If it will
be exported to Word, select "Rich Text Format (*.rtf)".
The format that is selected at this stage will be ending format
when it is exported.
- Click in the "Output File" field.
- Right-click in the "Output File" field and select "Zoom".
- Type the path where the file will reside once it is exported,
the filename, and the file extension. For example, c:\documents
and settings\all users\desktop\contact list.xls.
- Click on "Ok."
- Click in the "Auto Start" field. Select "Yes"
if the file is to open once it is exported. Select "No"
to have the end-user open the file once it is exported.
Do not forget to save the macro. To preview the macro’s operation,
click the exclamation point in the icon menu. To step through each
action of the macro, select the icon to the right of the exclamation
point (indented block paragraph with the arrow).