Export Recordset to Excel or CSV

Posted on 2011-10-21
Last Modified: 2012-05-12
There is a VERY easy way to export a saved MS Access query (via VBA) to an Excel file.  Basically, one line of code:
       DoCmd.OutputTo acOutputQuery, "query name", acFormatXLS, "outputfilename", 0

This works great if you have a saved query.  But I need to export a recordset, not a saved query.  I keep thinking that there must be a simply way to do this (just like exporting a saved query).  But I cannot seem to find anything on-line and the few things I've tried don't seem to work.

In the sample below, I create a recordset subset of records from a saved query. I've simplified the syntax, but I know that it's working because the msgbox returns a count of 50 records.  So, the recordset has data.

Next, I tried using the basic query export command, replacing the query reference with the recordset name.  But that doesn't seem to work.  I get a message indicating "The expression you entered is the wrong data type for one of the arguments

    Dim RS As DAO.Recordset
    Dim sSQL As String
            sSQL = "Select * from queryA where fieldX = 100"
            Set RS = CurrentDb.OpenRecordset(sSQL)
                RecCount = RS.RecordCount
                MsgBox RecCount
            If RecCount > 0 Then
                DoCmd.OutputTo acOutputQuery, RS, acFormatXLS, MyPathFileName, 0
            End if
Is there a way to export a recordset like this?  The other option would be to just save the query to the DB and export that saved query.  Seems like there should be a way to do this.

Question by:careybroph
    LVL 119

    Accepted Solution

    try this codes

    Dim db As dao.Database, rs As dao.Recordset
    Dim iRow, iCol
    Dim ssql As String
    Dim xlObj As Object
    Dim Sheet As Object

    Set db = DBEngine.Workspaces(0).Databases(0)

    ssql = "Select * from queryA where fieldX = 100"

    Set rs = db.OpenRecordset(ssql, dbOpenDynaset)

    Set xlObj = CreateObject("Excel.Application")
    Set Sheet = xlObj.activeworkbook.Sheets(1)
    'copy the headers

    iRow = 2
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(iRow, iCol + 1).Value = rs.Fields(iCol).Name

    Sheet.Range("A2").CopyFromRecordset rs  'this copy just the data
    xlObj.Visible = True
    LVL 61

    Assisted Solution

    Excel Automation.

    This is a generic function I use:

    Function GenericExcelReport(sSelect As String, sTitle As String) As Boolean
    'On Error GoTo ErrGenericExcelReport
        GenericExcelReport = False
        Dim db As Database
        Dim rsGeneric As DAO.Recordset
        Set db = CurrentDb
        Set rsGeneric = db.OpenRecordset(sSelect, dbOpenDynaset, dbSeeChanges)
        Dim ColCount As Integer
        Dim col As Integer
        Dim row As Integer
        Dim oExcel As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oWS As Excel.Worksheet
        'open the spreadsheet for editing
    'On Error GoTo Excel_EH
        If oExcel Is Nothing Then Set oExcel = New Excel.Application
        oExcel.Visible = True
        Set oWB = oExcel.Workbooks.Add
        Set oWS = oExcel.ActiveSheet
    'On Error GoTo ErrGenericExcelReport
        ColCount = rsGeneric.Fields.Count
        row = 1
        col = 0
        With oWS
            If (sTitle & "" <> "") Then row = row + 2       'set up for the title if there is one
            .Rows(row).Font.Bold = True
            'set up the Column Headings and
            Do While (col < ColCount)
                .Cells(row, col + 1).Value = rsGeneric.Fields(col).Name
                'check if this field type is Date/Time
                If rsGeneric.Fields(col).Type = 8 Then
                    'next line requires more checking, the property may not exist for each date field
                    'If (rsGeneric.Fields(col).Properties("Format") = "Short Date") then .Columns(col + 1).NumberFormat = "m/d/yyyy;@"
                    .Columns(col + 1).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
                End If
                'check if this field type is Currency
                If rsGeneric.Fields(col).Type = 5 Then
                   .Columns(col + 1).NumberFormat = "$#,##0.00"
                End If
                col = col + 1
            'output the data
            If rsGeneric.EOF Then
                row = row + 1
                col = 0
                .Cells(row, col + 1).Value = "There are no records to display."
                .Range(.Cells(row, col + 1), .Cells(row, ColCount)).Merge
            End If
            Do While Not rsGeneric.EOF
                row = row + 1
                col = 0
                Do While (col < ColCount)
                    .Cells(row, col + 1).Value = rsGeneric.Fields(col)
                    col = col + 1
            If (sTitle & "" <> "") Then
                row = 1
                col = 0
                .Rows(row).Font.Bold = True
                .Cells(row, col + 1).Value = sTitle
                .Cells(row, col + 1).WrapText = False
                .Cells(row, col + 1).Font.Size = 14
            End If
        End With
        GenericExcelReport = True
    Exit Function
        MsgBox "An error occurred. Please close excel and try running the process again.", vbExclamation, "No Page Break Inserted"
    Exit Function
        MsgBox "An error occured while attempting to generate the report." & vbCrLf & Err.number & ": " & Err.Description
    Exit Function
    End Function

    Open in new window

    You'd call it like this:

      sSQL = "Select * from queryA where fieldX = 100"
      GenericExcelReport sSQL, "This is your report title"
    LVL 61

    Expert Comment

    The code I posted requires you to set a reference for excel from the VBA editor:

    Tools -> References -> check your Microsoft Excel library

    Author Closing Comment

    Thank you.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now