• 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?
0
mr_anusorn
Asked:
mr_anusorn
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Use the CopyFromRecordset method.

gobjExcel.Sheets("Budget").Range("E10").CopyFromRecordset rst
0
 
CraigLazarCommented:
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
        rsRead.MoveFirst
        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")
            rsRead.MoveNext
        Loop
            wb.Close SaveChanges:=True
            xl.Quit
            Set xl = Nothing
   
    End If
   
    MsgBox "finnished"
       
eh:
    If Err.Number = 1004 Then
        MsgBox "Excel File is open in excel on the server"
        Exit Sub
    End If

End Sub

hope this helps

:)
Craig
0
 
mathiesCommented:
Transfert you recordset into a array.
After you can directly transfer you array into Excell
0

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