export vba query into excel

[tomvdduin]

I want to export a query build up in vba to Excel.
I thought to do it with
DoCmd.OutputTo acOutputQuery, strSQL,acFormatXLS, "c:\test.xls"

but that doesn't work. strSQL contains the sql-query

Can you help me?

greetz

Tom

 

[GHolden]

Hi,

The problem is that OutputTo (as with TransferSpreadsheet) will only accept Tables or Queries as their source. So you either need to make a Query or you could use automation to open excel and write the data a line at a time, which will take a fair bit more progamming.

There are two ways to write error-free programs; only the third one works.

 

[GummowN]

You could try this:

Create query call exportsql

In code have the following:

Dim dbs as database, qdf as recordset

set dbs = currentdb
set qdf = dbs.querydefs("exportsql&quot
qdf.sql = sqlstr
DoCmd.OutputTo acOutputQuery, qdf,acFormatXLS, "c:\test.xls"

If that doesnt work swap qdf for "exportsql"