Export Select Qry to Excel

[J360]

I'm using Access/Excel 03. I've created a query that opens fine, but when I use docmd.transferspreadsheet I receive ERROR 3011. Jet can't find the query qryMyQuery.

 Set db = CurrentDb
Set qdf = db.QueryDefs("qryMyQuery")
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenQuery "qryMyQuery"
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMyQuery", "C:\Documents and Settings\Name\Desktop\test.xls"
Set db = Nothing
Set qdf = Nothing

Can anybody tell me what I need to do so that it recognizes the query? Thanks!

[ADezii]

Try:

1. Explicitly Declare your Database Object Variable.
   

   Dim db As DAO.Database

2. Explicitly Declare your QueryDef Object Variable.
   

   Dim qdf As DAO.QueryDef

3. Declare a String Variable to hold the SQL Statement.
   

   Dim strSQL As String

4. Assign a SQL Statement to strSQL prior to setting the SQL Property of the QueryDef Object.
   

   strSQL = "Select * From Employees"
    
   qdf.SQL = strSQL

5. In toto, not the dog in the Wizard of Oz!
   

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim strSQL As String
    
   Set db = CurrentDb
   Set qdf = db.QueryDefs("qryEmployees")
    
   strSQL = "Select * From Employees"
    
   qdf.SQL = strSQL
   qdf.Close
    
   DoCmd.OpenQuery "qryEmployees"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryEmployees", "C:\ADezii\testamundo11.xls"
    
   Set db = Nothing
   Set qdf = Nothing
    

6. Any further questions, feel free to ask.

[J360]

Thanks, works great now. I just had to move the position of my set db and set qdf.

[ADezii]

Thanks, works great now. I just had to move the position of my set db and set qdf.

Glad it worked out for you.