Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Subroutine to export query results and open Excel File

Posted on 2012-04-01
7
Medium Priority
?
259 Views
Last Modified: 2012-04-01
I have gotten a lot of help on EE with creating a form that will allow the user to automatically import a spreadsheet into a table.  

Now, I'd like to create a subroutine that I can call from a button that will export a query (based on this imported table) into Excel, then open the Excel document.

Can someone help with this?
0
Comment
Question by:BBlu
  • 4
  • 3
7 Comments
 
LVL 9

Accepted Solution

by:
suvmitra earned 1400 total points
ID: 37794585
Use the default Macro available in MS Access for exporting a query to excel sheet. or use the code.

Dim Output_Path_And_File As String
Output_Path_And_File = "C:\ThePath\TheFile.xlsm"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QueryName", Output_Path_And_File, True,""
0
 

Author Comment

by:BBlu
ID: 37794589
I think I'll use your method, but could you tell me where to find the default macro.
0
 
LVL 9

Expert Comment

by:suvmitra
ID: 37794598
You go to Macro -- in MS Access -- Create New Macro -- there you will find options like transfer spreadsheets or output to ..etc
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:BBlu
ID: 37794618
I don't see that option on the Ribbon
Access-Ribbon.jpg
0
 
LVL 9

Expert Comment

by:suvmitra
ID: 37794626
Can you show me what are there in the Create Tab?

Macro
0
 

Author Comment

by:BBlu
ID: 37794652
Oh, I see that. Sorry.  I saw it and selected the "ExportWithFormatting" option.  After playing with it for a while, I got it to work.  Thanks.
0
 

Author Closing Comment

by:BBlu
ID: 37794654
Tanks, suvmitra!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

971 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