Results 1 to 12 of 12
  1. #1
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21

    Export Query Result to Excel

    I have a query, AppendNW, and I would like to press a button that runs the query, and the exports the results to my Excel WB call, Result, on sheet, Results1.



    I have found a handful of examples around the web, but everything seems complex based around formatting. I just need the data dumped into the WB so really was just beating around for a quick and easy means. Thanks

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is a sample of what I have used to export queries to excel. This one was designed around Office 2002, so you will have to amend the path for newer versions. Also you may want to update the docmd line to reflect newer versions of Excel to export to. But, it should give you a good heads up on how to go.

  3. #3
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21
    Alan,
    I am lost in the VB. I have 2 queries (more will come) Append and Delete. I like the way this DB is set up because it seems structure to the point where I could create a select tool like the select report to pick a query. However I do not know how to edit this in order to add queries:
    Private Sub Command3_Click()
    On Error GoTo Err_Command3_Click

    Dim stDocName As String

    stDocName = "Monthly Activity"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_Command3_Click:
    Exit Sub

    Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click

    End Sub


    Next for the Export part and its corresponding section, how do I go about setting that up once the select report button actually knows which query I have selected.

    Private Sub Command5_Click()
    On Error GoTo Err_Command5_Click
    Dim reportName As String
    Dim theFilePath As String

    Select Case Me.Frame6.Value
    Case 1
    reportName = "MonthlyActivity"
    End Select

    theFilePath = Me.txtfilepath.Value
    theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

    MsgBox "Look on your desktop for the report."



    Exit_Command5_Click:
    Exit Sub

    Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click

    End Sub


    Additionally why couldn't :


    Sub Export()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Append", "D:\PhotoDB\eortazontes.xls", True
    End Sub

    Like that work?

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Sub Export()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Append", "D:\PhotoDB\eortazontes.xls", True
    End Sub
    What happens when you execute this code? Does it work?


    Appears ok. You may want more advanced options as you move forward.

  5. #5
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21
    Quote Originally Posted by alansidman View Post
    What happens when you execute this code? Does it work?


    Appears ok. You may want more advanced options as you move forward.
    The query cannot be used as a row source.

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Suggest you post your database with dummy data so that we can see the whole picture. Post it as an earlier version, perhaps 2002 so that many can open and review. AC2010 is not used by all on this forum.

    Alan

  7. #7
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21
    Quote Originally Posted by alansidman View Post
    Suggest you post your database with dummy data so that we can see the whole picture. Post it as an earlier version, perhaps 2002 so that many can open and review. AC2010 is not used by all on this forum.

    Alan
    Alan,

    I made the simplest example I could dream up :P.

    I didn't start making the form as I couldn't get the module to run. But the final product would be a form with a drop down box, that lets you pick the Query you want to run, once it runs it exports it to an existing excel WB and Sheet. Another button runs the Macro I have, more will come.

    Hope this helps.

  8. #8
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I built a form for you. Look at how I set it up. Add more options and add them as part of the select case.

    Change the path to whatever you want.

    I don't think that you can export an Append or Delete query. What you would want to do then was run another select query and export the results or export the updated table.

  9. #9
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21
    Thanks for the help, however I can't get your DB to work. Did it export either of those Queries to your desktop?

    Additionally I have attached the working hard coded button, but can not get the option button working. Any ideas?

  10. #10
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Just noticed you are on XP operating system. The path for XP is different from Win7.
    Code:
    thefilePath ="C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"
    Try that and see if it works for you. That is what I have used on my XP machine and it works there. The path to the desktop in WIN7 is different and I am having issues with that.
    Alan

  11. #11
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I have it solved:
    Code:
    Private Sub Command3_Click()
     
        Dim reportName As String
        Dim theFilePath As String
     
    ''    On Error Resume Next
        Select Case Me.Frame4.Value
            Case 1
                reportName = "Query1"
            Case 2
                reportName = "Query2"
        End Select
     
        theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
        theFilePath = theFilePath & reportName & ".xlsx"
     
        DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True
        MsgBox "Check your Desktop."
     
    End Sub
    This works on my machine. If you change the first line of the path as I indicated in the earlier post for an XP and change the DoCmd as shown here, it should give you what you want.

  12. #12
    chewbears is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    21
    Alan, thank you for your persistance. It payed off. Just got back after the holidays and it worked like a charm!

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

Similar Threads

  1. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  2. Export a pass-through query to Excel
    By broon in forum Import/Export Data
    Replies: 0
    Last Post: 03-07-2011, 03:43 PM
  3. Export query to Excel
    By dev82 in forum Queries
    Replies: 15
    Last Post: 02-10-2011, 11:15 AM
  4. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 AM
  5. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 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