Results 1 to 8 of 8

Thread: Export Query to Excel

  1. #1
    Leon_SF is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5

    Export Query to Excel

    Hello -

    I'm really new at this, and I've gotten some really great help here before, both to a question I've posted, and also just by checking out other threads. I've tried to find the answer to my current problem, but haven't found one that quite matches (probably more due to my own inexperience than the solutions I've looked at...)

    I have a form that allows the user to select a query using a combination of two combo boxes. There is a button on the form that runs the selected query when clicked, and (surprisingly!) it works. However, I'd like to have the query results exported to an excel spreadsheet on the user's desktop instead of being returned in datasheet view in access. Here's the code that I have for the button:
    ---
    Private Sub Command274_Click()
    Dim ReportType As String

    If IsNull(Me.Combo172) Or IsNull(Me.Combo270) Then
    MsgBox "All the fields are required and can not be empty!"
    Else

    QueryType = Me.Combo172 & " " & Me.Combo270

    Select Case QueryType

    Case "Spreadsheet A"
    DoCmd.OpenQuery "Query_Spreadsheet_A"
    Case "Spreadsheet B"
    DoCmd.OpenQuery "Spreadsheet B"
    Case "Spreadsheet C"
    DoCmd.OpenQuery "Spreadsheet C"

    End Select
    End If

    End Sub
    ---

    I figure I need to use TransferSpreadsheet afgter the OpenQuery command to send the results to excel, and I have tried something like this:

    Case "Spreadsheet A"
    DoCmd.OpenQuery "Query_Spreadsheet_A"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C:\Desktop\SpreadsheetA.xls"

    but I'm doing something wrong, because it either doesn't work at all, or I get an error message. I appreciate any help I can get on this.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,383
    Can't save to Desktop. Save to the root level or to some folder path.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Leon_SF is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5
    Thank for the quick response!

    I changed the code to:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C:\Users\Leon\Documents\SpreadsheetA.xls"

    but I get an runtime error 3326 "this recordset is not updateable". Is there a problem with my code, or do you think there's a problem with the query? It runs fine when I don't try to export it...



    Quote Originally Posted by June7 View Post
    Can't save to Desktop. Save to the root level or to some folder path.

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,383
    Use latest Excel type reference: acSpreadsheetTypeExcel12

    If that doesn't work, do a test with a table or simple select query. If that works then definitely something wrong with the other query.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    Leon_SF is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5
    Thanks!
    I tried it, but am getting the same error; I will review the query to see what could be causing it.

  6. #6
    alansidman's Avatar
    alansidman is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,951
    When using 2007 and later, you need to use a slightly different convention. Look here.

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  7. #7
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,383
    I tested export of query with this syntax in both Access 2007 and 2010 and it worked:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "GamesSorted", "C:\Test.xls"
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    stephen9530 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    1
    There are many articles talking about data exporting to excel. I want to recommend one to you and I believe it's useful for you:

    http://www.codeproject.com/KB/cs/Exc...23#xx3768323xx

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

Similar Threads

  1. Export Query Result to Excel
    By chewbears in forum Access
    Replies: 11
    Last Post: 11-28-2011, 06:31 AM
  2. Replies: 3
    Last Post: 10-07-2011, 06:49 AM
  3. Export a pass-through query to Excel
    By broon in forum Import/Export Data
    Replies: 0
    Last Post: 03-07-2011, 01:43 PM
  4. Export query to Excel
    By dev82 in forum Queries
    Replies: 15
    Last Post: 02-10-2011, 09:15 AM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 07:22 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