TransferSpreadsheet, can I export a query instead of a table?

[xtreme69]

Want to export the results of a query, not a table, to Excel. Is there a method I can use besides TransferSpreadsheet that will do this, or does TransferSpreadsheet support this and I just don't know it? Thanks for the help in advance.

 

[hymn]

I use
DoCmd.RunCommand acCmdOutputToExcel
to output a the results of a Query

Hope this helps
Hymn

 

[SkipVought]

xtreme69,

I prefer to PULL data rather then PUSH.

I suggest PULLING the data from Excel via Data/Get External Data... and set up a query to your database.

You can also select Data Range Properties to do such things as Run on Open. Then you have a fresh resultset whenever Excel is Opened or you Refresh Data.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rbowes]

is the query a saved query or a sql statement? If it is a query then you can place the name of the query into the Table argument and it should work.

A sql statement requires a little more work. You could create a new querydef and append it to the querydefs collection then use the transferspreadsheet method. Then delete the querydef from the collection.

 

[xtreme69]

Thanks everyone. rbowes answer is what I was curious about, I hadn't tried putting the query name in place of the table argument and was wondering if it would work. I'll try now that I'm back from lunch.

As far as appending quuerydef's to a temp table goes, I've tried to stay away from that method because of the increase in file size and the need for database compacting much more frequently. Even when you delete the date from the table, you don't free up the space it used...bummer