Results 1 to 4 of 4

Export To Excel

  1. #1
    jadown is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    11

    Export To Excel

    All,

    I am getting a Run time error 3012: Object "AB_Planinng" already exists. I'm not sure where I messed up in the code. Please help.

    HTML Code:
    Sub ExportToXlsx()
        Dim cdb As DAO.Database, qdf As DAO.QueryDef
        Set cdb = CurrentDb
        Const xlsxPath = "C:\Users\Desktop\output" & "\AB_Planinng_AA" &  ".xlsx"
        
        ' create .xlsx file if it doesn't already exist, and add the first worksheet
        Set qdf = cdb.CreateQueryDef("AB_Planning", "SELECT [final_report].* FROM final_report")
        
        Set qdf = Nothing
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "AB_Planning", xlsxPath, True
        
        DoCmd.DeleteObject acQuery, "AB_Planning"
        
        Set cdb = Nothing
    End Sub
    It's been a long time since I have wrote VBA.

    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,948
    if the query already exists, you cant make another.
    you shouldnt be using this.
    instead,
    remove : DeleteObject
    remove: set qdf...

    theres no need to keep creating and deleting these objects.
    the query will be there everytime.

  3. #3
    jadown is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    11
    Thank you!!! That worked. Sorry it's been years!

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    rather than delete and recreate, which will result in some bloat, the alternative is to just change the qdef.sql

    currentdb.querydefs("AB_Planning").SQL="SELECT [final_report].* FROM final_report"

    Although with that particular sql, not sure why you wouldn't just export 'final_report' anyway

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-07-2016, 03:36 AM
  2. Export Queries to Excel with Combo-Box visible in Excel
    By johnmarc2 in forum Import/Export Data
    Replies: 1
    Last Post: 07-07-2014, 04:33 PM
  3. Replies: 7
    Last Post: 04-25-2013, 02:47 PM
  4. Replies: 3
    Last Post: 10-07-2011, 06:49 AM
  5. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 01:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums