Solved

Subroutine to export query results and open Excel File

Posted on 2012-04-01
7
249 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

21 Experts available now in Live!

Get 1:1 Help Now