Results 1 to 8 of 8
  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 VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't save to Desktop. Save to the root level or to some folder path.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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 VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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 offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    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

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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, 08:31 AM
  2. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  3. Export a pass-through query to Excel
    By broon in forum Import/Export Data
    Replies: 0
    Last Post: 03-07-2011, 03:43 PM
  4. Export query to Excel
    By dev82 in forum Queries
    Replies: 15
    Last Post: 02-10-2011, 11:15 AM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09: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
  •  
Other Forums: Microsoft Office Forums