Results 1 to 8 of 8

Thread: Export from ACCESS to Excel using VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2005
    Posts
    22

    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.

  2. #2
    Hyperactive Member
    Join Date
    Aug 2005
    Location
    Long Island, NY.
    Posts
    353

    Re: Export from ACCESS to Excel using VBA

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

    VB Code:
    1. Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    2.     FieldName As String, TargetRange As Range)
    3. ' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
    4.     "TableName", "FieldName", Range("C1")
    5. Dim db As Database, rs As Recordset
    6. Dim intColIndex As Integer
    7.     Set TargetRange = TargetRange.Cells(1, 1)
    8.     Set db = OpenDatabase(DBFullName)
    9.     Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
    10.     'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
    11.         " WHERE " & FieldName & _
    12.         " = 'MyCriteria'", dbReadOnly) ' filter records
    13.     ' write field names
    14.     For intColIndex = 0 To rs.Fields.Count - 1
    15.         TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    16.     Next
    17.     ' write recordset
    18.     TargetRange.Offset(1, 0).CopyFromRecordset rs
    19.     Set rs = Nothing
    20.     db.Close
    21.     Set db = Nothing
    22. End Sub

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2005
    Posts
    22

    Re: Export from ACCESS to Excel using VBA

    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.

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Export from ACCESS to Excel using VBA

    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.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2005
    Posts
    22

    Re: Export from ACCESS to Excel using VBA

    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

  6. #6
    New Member
    Join Date
    Feb 2010
    Posts
    1

    Smile Re: Export from ACCESS to Excel using VBA

    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!
    Last edited by peertje888; Feb 24th, 2010 at 03:31 PM.

  7. #7
    New Member
    Join Date
    Mar 2010
    Posts
    1

    Re: Export from ACCESS to Excel using VBA

    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?

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Export from ACCESS to Excel using VBA

    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.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width