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

 

[KenReay]

Hi Andrew

I have not tried this in the context of DoCmd.TransferSpreadsheet, but it may work

You can define parameters as a part of your query, by putting them in the SQL, before the SELECT, so

PARAMETER param1 text, param2 text
SELECT ...etc

then you can set values in these parameters using the parameter collection of the querydef

Dim Db as DAO.Database
Dim qdf as DAO.Querydef
'
Set Db = CurrentDb()
Set qdf = db.querydefs("YourQuery&quot
qdf.Parameters("param1&quot = "A"
qdf.Parameters("param2) = "B"

not saying this will work for what you want to do, just an outline idea


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[KenReay]

Hi Andrew

Another idea

You could build the SQL string for the query in code, and use the user enetred adata within the where clause eg

Dim Db as DAO.Database
Dim qdf as DAO.Querydef
Dim strSQL
strSQL = "SELECT ...etc"
'
Set Db = CurrentDb()
Set qdf = db.querydefs("YourQuery&quot
qdf.SQL = strSQL

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[AndrewWolford]

Ken,

I'm trying to write that query using your suggestion and it errors out. It say's "Invalid sql statement; expected DELETE, SELECT, UPDATE ..." here's what I wrote:

PARAMETER temp text, SELECT *
FROM testdata
group by temp;

Am I writing it wrong?

Thanks,

Andrew

 

[KenReay]

Hi

Yes, you have to terminate tkhe PARAMETER bit so

PARAMETER temp text;
SELECT *
FROM testdata WHERE FieldNAme = [temp];

it is worth looking in help for PARAMETER.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use