Export from ACCESS to Excel using VBA

Hi,
I want to export queries, pivot tables from ACCESS to EXCEL using VBA codes.
and i want to export them to specific places in excel spreadsheets. like i want to export two different query recordsets into one worksheet and put them in specific places. how to achieve that?

for now, i only have
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_lookup_ird", "c:\temp.xls", True

but i dont know to how to export pivot table using VBA, and i dont know how to put them in specific places.

thanks a lot in anticipation.

Why not export the data then create a pivot in excel? I found this:

VB Code:

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    FieldName As String, TargetRange As Range)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
    "TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
        " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Thanks a lot for the reply.
i will look into it.

but the reason that i want to export the pivot table is, i have created the pivot table in access, for viewing purpose. cos, i am going to view the reports and pivottables in access. only when i want to document them, i need to export all of them into one excel file.

anything that you think would help me with my export would be greatly appreciated.

Open access
OPen a recordset (rst)
Open excel
Add a new workbook
Get a ref to the sheet
Get a range object (rng) to the cells where you want the table to display.
Use Excels method to copy a recordset : rng.copyfromrecordset rst

It will overwrite the cells with the recordset. If you are automating from Access, this shouldn't be a problem.

Oh, has to be less than 65000 records returned too.

Hi, what i have tried is this, can help me out with the missing part?

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sOutput As String

Dim rng As Range
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Const cTabTwo As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

sOutput = CurrentProject.Path & "c:\Book1.xls"

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)


sSQL = "select * from qry_Client_Ranking"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

Set rng = A1: G26 (How TO DEFINE THE RANGE OBJECT?)
rng.CopyFromRecordset rst

may i know how to correct the range object and complete this procedure? thanks

Hey, you can set a range as below:

Code:

---

Set rng = wks.Range("A1:I4000")

---

If you get an error that the range type is undefined, you have to check Excel in the "Refferences" from the "Tools" menu.

Here was my script:

Code:

---

    Dim rst As DAO.Recordset
    Dim customQuery As String
    Dim cnt As Integer
   
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
   
    Dim fileName As String

    Set appExcel = Excel.Application
    appExcel.Visible = True
    Set wbk = appExcel.Workbooks.Add
    Set wks = wbk.Worksheets(1)
    Set rng = wks.Range("A2:I4001")
    wks.Cells(1, 1).Value = "Generating data..."
   
    Set rst = CurrentDb.OpenRecordset(customQuery)
    If (rst.RecordCount > 0) Then
        cnt = 1
        For Each fld In rst.Fields
            wks.Cells(1, cnt).Value = fld.Name
            cnt = cnt + 1
        Next fld
        Call rng.CopyFromRecordset(rst, 4000, 26)
    End If
   
    fileName = ExportDir & "\Molenproductie" & DateTimeFrom & "-" & DateTimeTo & ".xls"
    wks.SaveAs (fileName)
   
    rst.Close
    Set rst = Nothing

---

PS Sorry for the grave digging, but nice thread!

This article was very helpful for me in what I am trying to accomplish. However, I would ALSO like to have the user specify the range for the export in the same way you do when creating a fomrula in Excel. Does anyone have some tips for me in that regard?

What I posted before puts in a specific place... however you will need to tie it together somehow (like how does access know which excel and sheet to use and the cell location...?)

The problem with the specific place is that if it is not big enough, then it will overwrite rather than move the data out of the way.