Exporting a query's results

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

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

>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.

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

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?

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"

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.

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.

??