• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Exporting MS Access Query to Excel

I have a query created in MS Access that I would like to export to Excel.  Can you provide me with the VBA code which will export the query to Excel, save it to a specific location on my network drive and automatically open it for viewing?   Thanks.
0
alpha100
Asked:
alpha100
  • 4
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
to export the query to excel, use this line

for A2007 and above
docmd.transferspreadsheet acexport, 10, "queryName", "C:\FolderName\Myexcel.xlsx", true


for A2003
docmd.transferspreadsheet acexport, 8, "queryName", "C:\FolderName\Myexcel.xls", true
0
 
Rey Obrero (Capricorn1)Commented:
to open the excel file

shell "excel.exe C:\FolderName\Myexcel.xls"
0
 
Dale FyeCommented:
The OutputTo method will also do this:

docmd.OutputTo acOutputQuery, "queryName", "acFormatXLSX", [OptionalFileName], True

If you don't enter the output format (acFormatXLSX) it will popup a dialog that allows you to select your output format.  If you don't include a file name, it will popup the FileOpenSave dialog.  The True argument opens the file in it's native format.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alpha100Author Commented:
Can you provide me with the step by step instructions on accessing the VBA module in Access?   Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
open your db
hit ALT + F11
0
 
Dale FyeCommented:
1. Open a form in design view

2. Add a command button (turn off the control wizard or you will get the wizard solution).

3. Right click on the button and display the properties window.

4. In the Events tab, find the click event.  Expand the dropdown at the end of that line and select [Event Procedure], then click the "..." to the right of the dropdown.  That will open the VBA window.  (the other way to open the VBA window is Ctrl-G).

5.  Copy the code I gave you above into the click event of the control, making the appropriate changes to "queryName"

go back to your form and view it in form view.
0
 
alpha100Author Commented:
Can you only perform this in a form or can you do this from your switchboard?
0
 
Rey Obrero (Capricorn1)Commented:
depending on what switchboard you have..

better if you upload a copy of the db.
0
 
alpha100Author Commented:
Thanks so much for your assistance
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now