Solved

Creating a command to export records from a table to an Excel spreadsheet

Posted on 2013-01-17
4
283 Views
Last Modified: 2013-01-17
I would like to create a menu command that will export the records output from a query into an Excel spreadsheet. Currently this is accomplished by clicking on the "External Data" tab and selecting the "Excel" option (see attached). However, for the convenience of the user (who is not Access savvy),  I would like to create a command button in the menu that will accomplish the same thing. How would I do it?
0
Comment
Question by:geeta_m9
  • 2
  • 2
4 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 38787864
use this code

docmd.transferspreadsheet acexport,10, "tablename","c:\folder\myexcel.xlsx", true
0
 

Author Comment

by:geeta_m9
ID: 38787882
What is the "10" parameter for?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38787894
you can also use

DoCmd.OutputTo acOutputTable, "tableName", acFormatXLSX, "c:\folder\Myexcel.xlsx", True


<What is the "10" parameter for? >
that is the excel format for A2007 and above

if you are using Excel2003, use

docmd.transferspreadsheet acexport,8, "tablename","c:\folder\myexcel.xls", true
0
 

Author Closing Comment

by:geeta_m9
ID: 38787952
I just tested it and it works great!

Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question