[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exporting a query's results

Posted on 2006-06-02
9
Medium Priority
?
551 Views
Last Modified: 2007-11-27
How can I get Access to export query results into Excel automatically? Where would I put it, within the query design or as a module, or ?

Thanks,
CE
0
Comment
Question by:Ceffel
  • 5
  • 4
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16817152
In VBA  code, that would be...
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel{your version here}, "Your Table Name", "Your File Name.xls", {True if you want column headers, False if not} , {"Excel spreadsheet named range, if applicable"}

You'll need to edit the { } stuff, and not type it literally.

Hope this helps.
-Jim
0
 

Author Comment

by:Ceffel
ID: 16817184
When you say {your version here} would I type 'Excel 97 SR-2'?, "Qry_Export Me", "Exported Data.xls", True,

what is an Excel spreadsheet named range?

Also, where would I type this code/command? Inside of a Module?


Thanks,
CE
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16817240
>When you say {your version here}
When you type the line in VBA, the Intellisense will give you a display of acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel97, etc that you select.

>what is an Excel spreadsheet named range?
If you don't know, then you can leave it blank.
Excel has the ability for you to assign names to ranges of cells, by selecting your cell(s) then typing a name in the box to the left of the formula editor.

>Also, where would I type this code/command? Inside of a Module?
If you want to do this manually, then File: Save As/Export, and follow the prompts will get you there.
If you want to do this automanually, then you'll have to decide how you want this to happen:  When the user clicks a button, at the end of a code block, etc.  You'll have to spell this out first.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ceffel
ID: 16817314
Automanually: I just wanted this to happen in my query design view when I click Run. Once I click Run (or exclamation point) it would automatically export the results into an excel file. I would like this feature in many of my queries. Maybe what I wanted is a little more complex than I thought since I'm not knowledgeable of VBA at all.

I think what you suggested above is probably just the easiest route. (File, Save As, Export into an outside file, etc.)

Thanks again,
CE

0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 16817345
> I just wanted this to happen in my query design view when I click Run.
Not possible.  A query can be executed, and will return the query results, but you cannot assign logic to a query that states what to do with the data once it is executed.
0
 

Author Comment

by:Ceffel
ID: 16817346
Since each query is 30k rows, it locks my computer up for a short period of time every time (By doing the Save As/Export) Would actually embedding that code save me from that? I'm just wondering if going through the trouble would actually save me going forward?
0
 

Author Comment

by:Ceffel
ID: 16817415
OK, thanks for the information and help. It also came back and told me the following message for a 28k row result:

"there are too many rows to output, based on the limitation specified by the output format or by Microsoft Access"



0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16817465
Thanks for the grade.  Good luck with your project.  -Jim

>"there are too many rows to output, based on the limitation specified by the output format or by Microsoft Access"
Show me the code you executed this with.  Excel can handle 64k rows, so this shouldn't be a problem.
0
 

Author Comment

by:Ceffel
ID: 16817937
I have a query built and specified 1 complete fiscal years data which is 28k rows. Once I said, Save As/Export....then I got that message. I don't understand either because I knew Excel could handle 65,536 rows.

??

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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