none
Access 2010 Export data to Excel

    Question

  • I would like to create a button on a form that exports the data and headers from an Access query into a newly created spreadsheet in Excel.

    My query is called query1 and my spreadsheet just needs to be copied under the c:\ drive.

    Can anyone help?

    Monday, November 19, 2012 7:57 PM

All replies

  • You need a reference to Excel and then:
    Sub Export(rs As Recordset)
    Dim xlApp As New Excel.Application
        xlApp.FileNew
        xlApp.Range("A1").CopyFromRecordset rs
        xlApp.ActiveWorkbook.SaveAs "C:\Delete Me.xlsx"
        Set xlApp = Nothing
    End Sub



    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, November 19, 2012 8:10 PM
  • Thanks Rod, where do I reference my query?
    Monday, November 19, 2012 9:19 PM
  • You need to create a recordset based on the query and pass teh recordset to Export (the rs bit).

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, November 20, 2012 1:49 AM
  • Thanks Rod, where do I reference my query?

    Rod use early binding

    Dim xlApp As New Excel.Application

    So you should add references from VBA developer Menu/Tools/References -> "Microsoft Excel X.0 Object Library"


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, November 20, 2012 8:43 AM
  • Please could you give an example of how I would create a recordset for a query containing two fields

    a) ID

    b) Name

    Thanks

    Tuesday, November 20, 2012 8:39 PM
  • Dim c As New ADODB.Connection
    Dim d As New ADODB.RecordSet
    const Youre_Connection_string$ = "paste Driver you use"
    const sql$ = "select t.id, t.name from database_name t where t.somthing_as_key = 1"
    
        If c.state <> 1 Then c.Open Youre_Connection_string
        If d.state = 1 Then d.Close
        d.Open sql, c, adOpenStatic, adLockReadOnly
    
    While Not d.EOF
    debug.print d!id & " and " & _
                d!name 'you can do it whatever you want with this data now they view in immediate 
    Wend
    
        d.Close
    One of ADO samples

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, November 21, 2012 9:32 AM
  • Apologies for my inexperience but where abouts do I create the following reference to Excel

    Sub Export(rs As Recordset)
    Dim xlApp As New Excel.Application
        xlApp.FileNew
        xlApp.Range("A1").CopyFromRecordset rs
        xlApp.ActiveWorkbook.SaveAs "C:\Delete Me.xlsx"
        Set xlApp = Nothing
    End Sub

    And where do I add the following recordset

    Dim c As New ADODB.Connection
    Dim d As New ADODB.RecordSet
    const Youre_Connection_string$ = "paste Driver you use"
    const sql$ = "select t.id, t.name from database_name t where t.somthing_as_key = 1"
    
        If c.state <> 1 Then c.Open Youre_Connection_string
        If d.state = 1 Then d.Close
        d.Open sql, c, adOpenStatic, adLockReadOnly
    
    While Not d.EOF
    debug.print d!id & " and " & _
                d!name 'you can do it whatever you want with this data now they view in immediate 
    Wend
    
        d.Close

    Thanks

    Saturday, November 24, 2012 12:20 PM
  • No no - my 2nd part it show you how make it from Excel.

    But you do not know nothing about SQL or Connecion stings, do you?

    Read it first [links]. We do not seen any from you're ACC d.base.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, November 25, 2012 4:48 PM
  • Cheers for the info Oskar - I used the following code

    Private Sub ExportQuery_Click()
    On Error GoTo Err_ExportQuery_Click
    Dim reportName As String
    Dim theFilePath As String
    
    
        reportName = "MonthlyActivity"
    
    
           
    theFilePath = "C:\Drive"
    theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
          
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True
    
    
    MsgBox "Report has been run."
    
    
       
    
    
    Exit_ExportQuery_Click:
        Exit Sub
    
    
    Err_ExportQuery_Click:
        MsgBox Err.Description
        Resume Exit_ExportQuery_Click
        

    Sunday, November 25, 2012 6:07 PM
  • Hi Rod

    This will not export the headers and without creating the recordset first nothing will happen.

    Instead of all your code I'd use following single line of code:

    Docmd.TransferSpreadsheet acExport, , "query1", "C:\Temp\YourFileName.xlsx", True

    If you are running Win Vista or later you will not have write Access to C:\. That's why I used C:\Temp\ instead. Ensure C:\Temp\ exists before running the code

    HTH

    Henry

    "Rod Gill MVP" schrieb im Newsbeitrag news:8461cf3d-2a80-4fd7-bce0-f58bca9039e5@communitybridge.codeplex.com...

    You need a reference to Excel and then:

    Sub Export(rs As Recordset)
    Dim xlApp As New Excel.Application
         xlApp.FileNew
         xlApp.Range("A1").CopyFromRecordset rs
         xlApp.ActiveWorkbook.SaveAs "C:\Delete Me.xlsx"
         Set xlApp = Nothing
    End Sub

    Rod Gill

    The one and only Project VBA Book <http://www.projectvbabook.com/>

    Rod Gill Project Management <http://www.project-systems.co.nz/>

    Wednesday, November 28, 2012 6:00 AM