Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: Exporting from ACCESS to EXCEL

    Hello!

    I'm trying to export a table using VBA code:
    DoCmd.OutputTo acOutputTable, "temp", "MicrosoftExcelBiff8(*.xls)", filename

    I get an error message: Runtime error 2306: Too many rows...

    I have found this link:
    http://www.dbforums.com/archive/inde...t-1074386.html

    He has the same problems that I have, but he didn't received any answer!

    I use ACCESS 2003!
    The table has more than 20,000 row, but less than 65,535 row!

    Can somebody help me!
    Thanks!

  2. #2
    Join Date
    Apr 2008
    Posts
    2
    I have the resolution:

    Sub teszt()

    Dim cn As Object

    Dim xlApp As Object
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim xlBook As Excel.Workbook
    Set xlApp = CreateObject("Excel.application")

    xlApp.Workbooks.Add
    xlApp.ActiveWorkbook.SaveAs FileName:=""c:\test.xls""
    xlApp.ActiveWorkbook.Close

    Set xlBook = xlApp.Workbooks.Open("c:\test.xls")

    Set cn = CurrentProject.Connection
    sql = "select * from temp"
    rs.Open sql, cn, 3
    xlApp.ScreenUpdating = False
    xlApp.Calculation = -4135
    For i = 0 To rs.Fields.Count - 1
    xlApp.cells(1, i + 1) = rs.Fields(i).Name
    Next
    xlApp.range("a2").copyfromrecordset rs
    xlApp.StatusBar = False
    xlApp.ScreenUpdating = False
    xlApp.Calculation = -4105
    xlApp.ActiveSheet.usedrange.entirecolumn.autofit
    xlBook.Save
    xlBook.Close
    rs.Close
    xlApp.Quit

    End Sub

  3. #3
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    bravo!!! asking question and answering to yourself
    here is a link to some good codes for Excel automation in Code Bank by Pootle Flump
    http://www.dbforums.com/showpost.php...81&postcount=4

Posting Permissions

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