combine runsql with transferspreadsheet

[p0welly]

in my application I have a number of options where the user can transfer data out of the program into a spreadsheet, I would like to create a dynamic option to do this but Im not really sure how to go about this.

strSQL = Me.txtSQL.Value
If strSQL Like "Select*" Then
    strSQL = Me.txtSQL.Value
    DoCmd.RunSQL strSQL
    DoCmd.TransferSpreadsheet acExport, , THESQLRESULTGOESHERE, "C:\Program Files\MYAPPLICATION\Exports\sqldump.xls", True
 APPNAME

I thinking something like this, but im guessing it cant be that simple.

Thanks in advance

Andy

 

[p0welly]

Figured it out based on this thread.

On Error Resume Next

Dim db As DAO.Database
Dim qdfNew As QueryDef
Dim strSQL As String
strSQL = Me.txtSQL.Value

Set db = CurrentDb()

With db
.QueryDefs.Delete "qrySQLDump"
Set qdfNew = .CreateQueryDef("qrysqldump", strSQL)
.Close
End With

DoCmd.TransferSpreadsheet acExport, , "QrySQLDump", "C:\Program Files\MYAPPLICATION\Exports\sqldump.xls", True

Seems the easy way is to create a query on the fly and then transfer the query as a spreadsheet