Solved

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

Posted on 2013-01-17
4
282 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

Outlook Free & Paid Tools
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

860 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