none
ACCESS 2007: Using VBA to export a table or recordset into Excel 2007

    Question

  • In Access 2007, can I use VBA to export a table to Excel 2007?  Even better, can I use VBA to export a recordset to Excel 2007?  If so, please give me a sample code.  I'm trying to automate some routine database functions and this is one of the things on my list.  Thanks so much
    David Lee Wisniewski
    Wednesday, February 02, 2011 8:40 PM

Answers

  • The following is a demo of how to create a DAO recordset on an Access query and then save the data in the recordset to an Excel workbook.  Copy the following code to a new standard module in the Access database.  It's pretty straightforward as the Excel object library has a CopyFromRecordset method (which belongs to the Excel Range object).

    Geoff

     

    Option Compare Database
    Option Explicit
    
    
    '  This module requires references to the
    '  following object libraries:
    '
    '  1. Microsoft Excel X.X Object Library,
    '    where X.X is the Excel Version Number.
    '
    '  2. One of the following:
    '
    '    For mdb files:
    '      Microsoft DAO 3.6 Object Library
    '      (DAO360.DLL)
    '    For ACCDB files (Access 2007):
    '      Microsoft Office 12 Access Database Engine Objects
    '      (ACEDAO.DLL)
    '      This reference should be set already.
    '
    '  To set the reference, in the VBA editor:
    '    Tools > References.
    
    
    Private Sub SaveRecordsetToExcelRange()
    
      '  Excel constants:
      Const strcXLPath As String = "C:\\MyData\MyWorkbook.xls"
      Const strcWorksheetName As String = "Sheet1"
      Const strcCellAddress As String = "A1"
      
      '  Access constants:
      Const strcQueryName As String = "MyQuery"
      
      '  Excel Objects:
      Dim objXL As Excel.Application
      Dim objWBK As Excel.Workbook
      Dim objWS As Excel.Worksheet
      Dim objRNG As Excel.Range
      
      '  DAO objects:
      Dim objDB As DAO.Database
      Dim objQDF As DAO.QueryDef
      Dim objRS As DAO.Recordset
      
      
      On Error GoTo Error_Exit_SaveRecordsetToExcelRange
      
      '  Open a DAO recordset on the query:
      Set objDB = CurrentDb()
      Set objQDF = objDB.QueryDefs(strcQueryName)
      Set objRS = objQDF.OpenRecordset
      
      '  Open Excel and point to the cell where
      '  the recordset is to be inserted:
      Set objXL = New Excel.Application
      objXL.Visible = True
      Set objWBK = objXL.Workbooks.Open(strcXLPath)
      Set objWS = objWBK.Worksheets(strcWorksheetName)
      Set objRNG = objWS.Range(strcCellAddress)
      objRNG.CopyFromRecordset objRS
      
      '  Destroy objects:
      GoSub CleanUp
      
    Exit_SaveRecordsetToExcelRange:
    
      Exit Sub
      
    CleanUp:
    
      '  Destroy Excel objects:
      Set objRNG = Nothing
      Set objWS = Nothing
      Set objWBK = Nothing
      Set objXL = Nothing
      
      '  Destroy DAO objects:
      If Not objRS Is Nothing Then
        objRS.Close
        Set objRS = Nothing
      End If
      Set objQDF = Nothing
      Set objDB = Nothing
      
      Return
      
    Error_Exit_SaveRecordsetToExcelRange:
    
      MsgBox "Error " & Err.Number _
        & vbNewLine & vbNewLine _
        & Err.Description, _
        vbExclamation + vbOKOnly, _
        "Error Information"
        
      GoSub CleanUp
      Resume Exit_SaveRecordsetToExcelRange
    
    End Sub
    
    • Marked as answer by Bessie Zhao Thursday, February 10, 2011 9:45 AM
    Thursday, February 03, 2011 12:18 AM

All replies

  • Play with these.

    DoCmd.OpenQuery "NameOfYourQuery", acViewNormal, acReadOnly
    DoCmd.RunSavedImportExport "NameOfYourSavedExcelExport"
    DoCmd.Close 1, "NameOfYourQuery"

    Before you do, run your query and create and save an Excel Export.  That functionality is found on the "External Data" tab.

    Wednesday, February 02, 2011 8:50 PM

  • Unfortunately that is not going to work for me because I am trying to avoid having to do anything manually besides write the code and click a button.  There has to be a way to export a table/query/recordset from Access 2007 to Excel 2007 using VBA code... anything you can do in the GUI of access you can automate with code, I just can't find the code.
    David Lee Wisniewski
    Wednesday, February 02, 2011 9:45 PM
  • it also has to export into the excel 2007 format "xlsx" because the table/query/recordset contains more than 65,000 records.
    David Lee Wisniewski
    Wednesday, February 02, 2011 9:47 PM
  • If your table fields don't change you can run the same "saved" Excel export through VBA when you click a button using the code I supplied before.  Play with the Export Excel data and you'll see it might be what you're looking for.  If your looking to append data to an existing Excel file, that's a different question.
    Wednesday, February 02, 2011 10:00 PM
  • Ok, this is what I'm looking for.  In Access 2007 you can go to the External Data ribbon, then in the Export group you can click Excel, then go through the options and export a table into excel 2007 in the xlsx format (which isn't limited to 65,000 records).  If you can do that manually then I know there is a way to automate it from start to finish with VBA... just figuring it out is the question.  I don't want to do a "save excel export" thing.  I'm building a complex insurance database and I need to be able to use this code for multiple things and multiple databases.  I've tried the outputto code, but that is limited to 65,000 records, I've tried the transferspreadsheet thing and that is also limited to 65,000 records.  But when you manually export it via External Data ribbon it's not limited to 65,000 records.  There has got to be a way... maybe if I point to my database as an object I can get more options for automation I'm not sure... lost in space here haha.
    David Lee Wisniewski
    Wednesday, February 02, 2011 10:08 PM
  • The following is a demo of how to create a DAO recordset on an Access query and then save the data in the recordset to an Excel workbook.  Copy the following code to a new standard module in the Access database.  It's pretty straightforward as the Excel object library has a CopyFromRecordset method (which belongs to the Excel Range object).

    Geoff

     

    Option Compare Database
    Option Explicit
    
    
    '  This module requires references to the
    '  following object libraries:
    '
    '  1. Microsoft Excel X.X Object Library,
    '    where X.X is the Excel Version Number.
    '
    '  2. One of the following:
    '
    '    For mdb files:
    '      Microsoft DAO 3.6 Object Library
    '      (DAO360.DLL)
    '    For ACCDB files (Access 2007):
    '      Microsoft Office 12 Access Database Engine Objects
    '      (ACEDAO.DLL)
    '      This reference should be set already.
    '
    '  To set the reference, in the VBA editor:
    '    Tools > References.
    
    
    Private Sub SaveRecordsetToExcelRange()
    
      '  Excel constants:
      Const strcXLPath As String = "C:\\MyData\MyWorkbook.xls"
      Const strcWorksheetName As String = "Sheet1"
      Const strcCellAddress As String = "A1"
      
      '  Access constants:
      Const strcQueryName As String = "MyQuery"
      
      '  Excel Objects:
      Dim objXL As Excel.Application
      Dim objWBK As Excel.Workbook
      Dim objWS As Excel.Worksheet
      Dim objRNG As Excel.Range
      
      '  DAO objects:
      Dim objDB As DAO.Database
      Dim objQDF As DAO.QueryDef
      Dim objRS As DAO.Recordset
      
      
      On Error GoTo Error_Exit_SaveRecordsetToExcelRange
      
      '  Open a DAO recordset on the query:
      Set objDB = CurrentDb()
      Set objQDF = objDB.QueryDefs(strcQueryName)
      Set objRS = objQDF.OpenRecordset
      
      '  Open Excel and point to the cell where
      '  the recordset is to be inserted:
      Set objXL = New Excel.Application
      objXL.Visible = True
      Set objWBK = objXL.Workbooks.Open(strcXLPath)
      Set objWS = objWBK.Worksheets(strcWorksheetName)
      Set objRNG = objWS.Range(strcCellAddress)
      objRNG.CopyFromRecordset objRS
      
      '  Destroy objects:
      GoSub CleanUp
      
    Exit_SaveRecordsetToExcelRange:
    
      Exit Sub
      
    CleanUp:
    
      '  Destroy Excel objects:
      Set objRNG = Nothing
      Set objWS = Nothing
      Set objWBK = Nothing
      Set objXL = Nothing
      
      '  Destroy DAO objects:
      If Not objRS Is Nothing Then
        objRS.Close
        Set objRS = Nothing
      End If
      Set objQDF = Nothing
      Set objDB = Nothing
      
      Return
      
    Error_Exit_SaveRecordsetToExcelRange:
    
      MsgBox "Error " & Err.Number _
        & vbNewLine & vbNewLine _
        & Err.Description, _
        vbExclamation + vbOKOnly, _
        "Error Information"
        
      GoSub CleanUp
      Resume Exit_SaveRecordsetToExcelRange
    
    End Sub
    
    • Marked as answer by Bessie Zhao Thursday, February 10, 2011 9:45 AM
    Thursday, February 03, 2011 12:18 AM
  • Hi,

    Can you tell me why that when I try to run this code I get the following error:
    "Error 3265 Item not found in this collection"

    I'm using Windows 7, Access 2010 and Excel 2010.

    Thank you!

    Cee

    Tuesday, September 24, 2013 11:59 PM
  • The code uses QueryDefs, Workbooks and Worksheets collections. 

    Therefore, carefully check that you have correctly named the Query, Workbook and Worksheet in your code.

    To identify the code line that is generating the error, follow these steps:

    1. Temporarily disable the error handler by inserting an apostrophe before the following code line: ' On Error GoTo Error_Exit_SaveRecordsetToExcelRange
    2. Click the mouse anywhere in the subprocedure.
    3. Press F8 — the F8 Function Key — repeatedly to run the code one line at a time.
    4. When an error is generated, you will have found the line in which the code expects to find an item in a collection, but the item is missing. 
    5. Examine the code line to identify what item is missing from the collection.
    6. If you still need help, post the code line that is causing the error.
    7. When you are finished, remove the leading apostrophe from the "On Error" code line.

    Geoff

     


    Wednesday, September 25, 2013 8:54 PM
  • There are several examples here.

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    This is really good too.

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, September 29, 2013 10:01 AM