Export Query Results from MS Access into MS Excel

Question

I want to write a macro on a Command button on my switchboard in MS Access, and I want that command button to have a Macro to Export all of the results of a query I have and then output it into a MS Excel, i.e. I want it to open up Excel and then copy all of the results from the query into MS Excel, but I need to Command button in MS Access, to do all of this, any guidance will be much appreciated! If you don't know how to open it, but can save all of the results in Excel, that would also be helpful! Thanks!

Answer 1

Access actually has a built-in menu option to do just this.  While viewing the results of your query, go to Tools > Office Links > Analyze w/ Excel.  This will export your query results into an Excel spreadsheet.

(above menu option is based on Office 2003.  Office 2007 has this feature as well, but may be under a different menu)

Answer 2

I think access provides a builder to help you build your command buttons, in that builder there is an option to export to excel. Try that, if it works then great! If not, let me know and I'll help you work it out a different way.

Edit:

--------------------------------------...

I don't understand why you asked the same question again, but I'll go ahead and answer you on both to be sure you get this.

Let's create a macro:

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.

7. 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.

8. Click in the "Type" field.

9. 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.

10. 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.

11. Click on the second line for the next action.

12. Click on the drop-down arrow to select "OutputTo".

13. Click in the "Object Type" field or press "F6".

14. 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.

15. 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.

16. 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.

17. Click in the "Output File" field.

18. Right-click in the "Output File" field and select "Zoom".

19. 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.

20. Click on "Ok."

21. 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.

22. Save the Macro

Now that you have the macro, you can use the expression builder on your form control (button) onclick event to run this macro. Something like:

DoCmd.RunMacro "My Macro"

Where "My Macro" is the name of your macro.