exporting a query to excel

[AndrewWolford]

Hi all,

I'm trying to find a way to export a query to excel from Access and I'm having a bit of trouble. I am familiar with the following method but it seems to only work when using an existing, saved query:

DoCmd.TransferSpreadsheet acExport, 8, currQuery, outfile, True (where 8=excel'97 and outfile is the name of my excel file and currQuery is the saved, exsisting query)

The problem is that my query has a 'GROUP BY' clause that needs to be user defined (in other words the user selects what he want to group by) and this method requires that the query to be exported is saved as a query, thus not allowing the user to define anything. Ideally, I want to first create the sql statement in code (so that I can use a variable for the GROUP BY) and then execute that sql statement exporting it to excel. It seems like this should be possible to do. Does anyone know how to do this? Also, I'm always open to other suggestions on how to accomplish this. I'm sure there's more than 1 way to do this... Thanks for any help!

-Andrew

 

[VulcanJedi]

I wish I could offer more insight, but my only suggestion is that because I am pretty sure Access can't export filters and I think Group by is a 'filter' of sort, than you should:

1. You could group by after in excel, this I believe would be exempt from any issues. Could do manually or via code
2. Save/copy/export the query data into a new access table, this will work rather smoothly barring the records being excessive large. When you copy/paste it will preserve the filtering since you only copy the filtered data, then you could export cleanly?

Hope this helps although perhaps obscurely.