• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

Export a DAO recordset to excel?

How to easily export a DAO recordset to EXCEL within a few line of code?
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
Use the CopyFromRecordset method.

gobjExcel.Sheets("Budget").Range("E10").CopyFromRecordset rst
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

Transfert you recordset into a array.
After you can directly transfer you array into Excell

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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