Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2427
  • Last Modified:

Export Recordset to Excel or CSV

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)
                RS.MoveLast
                RS.MoveFirst
                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.

THANKS.
SMC
0
careybroph
Asked:
careybroph
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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")
xlObj.workbooks.Add
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
    Next


Sheet.Range("A2").CopyFromRecordset rs  'this copy just the data
xlObj.Visible = True
0
 
mbizupCommented:
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

       DoEvents
    
    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
        Loop
        
        '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
            Loop
                        
            rsGeneric.MoveNext
        Loop
        
        .Cells.EntireColumn.AutoFit
        
        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

Excel_EH:
    DoEvents
    DoEvents
    MsgBox "An error occurred. Please close excel and try running the process again.", vbExclamation, "No Page Break Inserted"
Exit Function

ErrGenericExcelReport:
    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"
0
 
mbizupCommented:
The code I posted requires you to set a reference for excel from the VBA editor:

Tools -> References -> check your Microsoft Excel library
0
 
careybrophAuthor Commented:
Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now