databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Export Data Macro

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.

  1. Click on the "Macros" option in the "Objects" menu.
  2. Click "New" in the upper-panel of the database window to create a new macro.
  3. Click in the "Action" field.
  4. Click on the drop-down arrow and go to "MsgBox".
  5. 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".
  6. 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…".
  7. Click "Ok" once the message has been typed.
  8. 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.
  9. Click in the "Type" field.
  10. 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.
  11. 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.
  12. Click on the second line for the next action.
  13. Click on the drop-down arrow to select "OutputTo".
  14. Click in the "Object Type" field or press "F6".
  15. 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.
  16. 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.
  17. 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.
  18. Click in the "Output File" field.
  19. Right-click in the "Output File" field and select "Zoom".
  20. 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.
  21. Click on "Ok."
  22. 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).