Use Dynamic SQL in TransferSpreadsheet

[yippie_ky_yay]

Hello,

I can use the TransferSpreadsheet method to get my query output into Excel - but is it possible to use a dynamic query?

For example, rather than:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMyQuery", "C:\My Documents\myQueryInExcel"

Could I do something like?:

Dim mySql as string
mySql = "Select * From myTable Where id = 4"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, mySql, "C:\My Documents\myQueryInExcel"

I know that the above doesn't work - hopefully it will illustrate what it is I'm trying to accomplish.

Thanks in advance,
-Sean

 

[GrahamB]

Try...

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim mySql As String
mySql = "Select mytable.* From myTable Where mytable.[id] = 4"
db.CreateQueryDef "temp", mySql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "temp", "C:\My Documents\myQueryInExcel"

It creates a query called "temp", which you can of course delete once you have output it.

 

[yippie_ky_yay]

Thank you Graham - this works great!

Cheers!
-Sean