Export a DAO recordset to excel?

How to easily export a DAO recordset to EXCEL within a few line of code?
Who is Participating?
CraigLazarConnect With a Mentor Commented:
Hi try this this is a sample from one of my projects

be sure to click the referenece ExcelCtl type library in your project refrences of your project

Private Sub cmdSendExcelex_Click()
On Error GoTo eh:
  Dim tmpStartDate As Date
  Dim tmpEndDate As Date
  Dim xl As New Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim tmpPercentCheck As String
  tmpStartDate = GetStartDate()
  tmpEndDate = GetEndDate()
  Dim db As Database
  Dim rsRead As Recordset
  Dim SQLRead As String
  Dim tmpCounter As Integer
  'Variables for the cells
    Dim tmpACell As String
    Dim tmpBCell As String
    Dim tmpCCell As String
    Dim tmpDCell As String
    Dim tmpECell As String
    Dim tmpFCell As String
    Dim tmpGCell As String
    Dim tmpHCell As String
    SQLRead = "Select * from Expenses where Date >= #" & tmpStartDate & "# and Date<=#" & tmpEndDate & "#;"
    tmpCounter = 0
    Set wb = xl.Workbooks.Open("c:\jimauto\Invoices.xls")
    Set ws = wb.Sheets("List Expenses")
    Set db = OpenDatabase("c:\Jimauto\Vat.mdb")
    Set rsRead = db.OpenRecordset(SQLRead, dbOpenSnapshot)
    If rsRead.RecordCount <> 0 Then
        tmpCounter = tmpCounter + 4
        ws.Range("D1").Value = tmpStartDate
        ws.Range("F1").Value = tmpEndDate
        Do Until rsRead.EOF
            tmpCounter = tmpCounter + 1
            tmpACell = "A" & tmpCounter
            tmpBCell = "B" & tmpCounter
            tmpCCell = "C" & tmpCounter
            tmpDCell = "D" & tmpCounter
            tmpECell = "E" & tmpCounter
            tmpFCell = "F" & tmpCounter
            ws.Range(tmpACell).Value = rsRead("Date")
            ws.Range(tmpBCell).Value = rsRead("Company")
            ws.Range(tmpCCell).Value = rsRead("Memo")
            ws.Range(tmpDCell).Value = rsRead("Cost")
            ws.Range(tmpECell).Value = rsRead("Vat")
            ws.Range(tmpFCell).Value = rsRead("Total")
            wb.Close SaveChanges:=True
            Set xl = Nothing
    End If
    MsgBox "finnished"
    If Err.Number = 1004 Then
        MsgBox "Excel File is open in excel on the server"
        Exit Sub
    End If

End Sub

hope this helps

Éric MoreauSenior .Net ConsultantCommented:
Use the CopyFromRecordset method.

gobjExcel.Sheets("Budget").Range("E10").CopyFromRecordset rst
Transfert you recordset into a array.
After you can directly transfer you array into Excell
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.