How to Send Query Results to Excel

[Guest]

I have produced a query that extracts a list of volunteers who helped this
food bank in the last two years. Mgmt want the results moved to Excel so it
can be combined with spreadsheets from contributors and other benefactors
soas to thank them with a letter. While my "Access 2003 Bible" claims it can
be done directly from a query, it provides no example. I think I am going to
have to create a table using ADOX, populate it, then export that file
manually to Excel. Can anyone help me to do this entirely automated versus
requiring manual intervention?

Earl Phillips
Volunteer, Self-Taught Programmer
Harvesters Community Food Network

 

[Alex Dybenko]

Hi,
I think you can use DoCmd.TransferSpreadsheet to export your query to Excel.
And to create table from a query - you can use make-table query

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Dim XL As Object
Set XL = CreateObject("Excel.Application")
DoCmd.OutputTo acOutputQuery, "qryVolunteers", acFormatXLS,
"C:\Totals.xls", True



Jim

 

[Guest]

I forgot to add that the target XL file would then have a macro that copies
and pastes the data into the proper places in the Main file.

Dim XL As Object

Set XL = CreateObject("Excel.Application")
DoCmd.OutputTo acOutputQuery, "qryVolunteers", acFormatXLS,
"C:\Totals.xls", True
Run Combine_results (or whatever you name your macro)

Jim

 

[Guest]

I tried your suggestion and while it did not fail or baulk at compile, it did
not produce a file. Report code on close was:
DoCmd.TransferSpreadsheet (acExport), acSpreadsheetTypeExcel7,
"qryMainWithActivityInLast2Yrs", "C:\Progra~1\Access~1\Test.xls

Also tried form with generated code of:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
which also did not baulk at compile and did not produce a file I could find.

Any other suggestions?

 

[Guest]

I tried this and it actually did work, in spite of my last post. I had sent
it to a different folder and did not realize it. Thank you for your help. I
also went to your blog and read more about the problem, which is how I
finally found the result.
Earl Phillips