Solved

Subroutine to export query results and open Excel File

Posted on 2012-04-01
7
248 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 350 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now