Save Report's Underlying Query to Excel

[hughesai]

I have a number of reports in A2K and A97. They are opened from a form and are filtered using values selected on this form.

I already have a button to export the report to snapshot format using the following code:

DoCmd.OutputTo acOutputReport, , "SnapshotFormat(*.snp)", stFile, False, ""

I also have a button to export the report to Excel using code:

DoCmd.OutputTo acOutputReport, , acFormatXLS, stFile, False, ""

All works well, but what I really want to do is export the underlying recordset of the report (i.e. the original query along with the filters set through my form) as an excel sheet.

The difference between this and exporting the report to Excel is that I do not want the output grouped - I want a distinct line for every record so that users may take the file and sort and filterit themselves.

I want this to be a generic piece of code like the above, so that I do not have to have different button bars for every different report.

Does anyone know how to do this?

 

[hughesai]

Have I beaten everyone ?
Surely someone here has some sort of ideas on this ?
Any help is greatly appreciated.

 

[MichaelRed]

If not 'beaten' at least one of us is not willing to devote pro-bono to development of the camel. Starting with a plate of cooked spaghetti, you want someone ELSE to detangle all of the strands, seperate the meat from the sauce, un-cook each ingredient, and finally re-write the menu? Any more expectations?

Is your name Huck Finn?


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

On the other hand, you should be able to identify the Reports "RecordSource" and export that to any of the standard output destintions (.XLS, .DOC, ...).

Application of the filters is the other matter.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[hughesai]

MichaelRed - Thank you for your response. I apologise if I have in some way offended you. I'm really not sure just where spagetti comes into the debate.

I'm only looking for some help here - I thought that's what this forum was about - sharing knowlegde and expertise. I'm pretty new here, but I have tried to help others as well as ask for help.

You are right - I can identify the record source, and also the filters, and with a bit of persuasion, I can combine them into one SQL string. However, I can not get

DoCmdOutput acOutputQuery ......

to accept a SQL string, it will only accept a query name. This is where I am currently stuck.

 

[MichaelRed]

A soloution would be to create a query - referr to it as "qryTemp". I makes NO difference what it is 'originally', although I would probably limit it to some inoccous select query and avoid anything which might alter data in any tables. Set a query def to qryTemp. and set the Sql property of qryTemp to the SQL string which you are able to generate. Export qurTemp. Boom! Done!! Finoito!!!


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over