How to Build and export a crosstab query in VBA?

[tetsuo2030]

Hi all,

What I'm trying to do is create a crosstab query (based on a temp table) in VBA, then export it to Excel. The temp table is built off a form where the user enters a parameter(s) in a textbox(es).

I created the crosstab query using the wizard and copied the code into my VBA (button click), but it's not an action query, so DoCmd.RunSQL won't work.

Then I tried:

Dim rst as Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

But where do I go from there?

I guess, really, I don't need to put it into a table if I'm just going to export it to Excel anyway, and it need not be a crosstab query: How do I create a SQL SELECT statement in VBA and display the results?

[tetsuo2030]

Guys, I got it:

Built a bogus query called "qry_index" (one field from one table).

Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim As String
    Dim rst As Recordset
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_index")
    strSQL = "TRANSFORM.....;"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qry_index"
    Set qdf = Nothing
    Set db = Nothing

[tetsuo2030]

I LOVE this stuff!

[mwgass]

This was quite helpful. Thanks!

[twinnyfo]

tetsuo2030,

If you already have the query built (which you can build it in the query builder to reference the controls on your form), there is also a much more easier way to export to Excel:

DoCmd.TransferSpreadsheet acExport, , "qry_index", "ExcelPathAndFileName", True, "ExcelWorksheetName"