By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

Export Select Qry to Excel

P: 23
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.

Expand|Select|Wrap|Line Numbers
  1.  Set db = CurrentDb
  2. Set qdf = db.QueryDefs("qryMyQuery")
  3. qdf.SQL = strSQL
  4. qdf.Close
  5. DoCmd.OpenQuery "qryMyQuery"
  6.  
  7.  
  8. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMyQuery", "C:\Documents and Settings\Name\Desktop\test.xls"
  9. Set db = Nothing
  10. Set qdf = Nothing
Can anybody tell me what I need to do so that it recognizes the query? Thanks!
Sep 18 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,627
Try:
  1. Explicitly Declare your Database Object Variable.
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
  2. Explicitly Declare your QueryDef Object Variable.
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
  3. Declare a String Variable to hold the SQL Statement.
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
  4. Assign a SQL Statement to strSQL prior to setting the SQL Property of the QueryDef Object.
    Expand|Select|Wrap|Line Numbers
    1. strSQL = "Select * From Employees"
    2.  
    3. qdf.SQL = strSQL
  5. In toto, not the dog in the Wizard of Oz!
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2. Dim qdf As DAO.QueryDef
    3. Dim strSQL As String
    4.  
    5. Set db = CurrentDb
    6. Set qdf = db.QueryDefs("qryEmployees")
    7.  
    8. strSQL = "Select * From Employees"
    9.  
    10. qdf.SQL = strSQL
    11. qdf.Close
    12.  
    13. DoCmd.OpenQuery "qryEmployees"
    14. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryEmployees", "C:\ADezii\testamundo11.xls"
    15.  
    16. Set db = Nothing
    17. Set qdf = Nothing
    18.  
  6. Any further questions, feel free to ask.
Sep 18 '08 #2

P: 23
Thanks, works great now. I just had to move the position of my set db and set qdf.
Sep 19 '08 #3

ADezii
Expert 5K+
P: 8,627
Thanks, works great now. I just had to move the position of my set db and set qdf.
Glad it worked out for you.
Sep 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.