Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How to export more than 65,000 records from 2007

    I'm using the code below to run my queries and export to Excel. I've tried several file types but they all bomb out if the query comes back with more than 65,000 records.

    Any ideas on how I can fix this?




    Private Sub Command0_Click()
    Dim qdftemp As QueryDef
    Dim a As String
    For Each qdftemp In CurrentDb.QueryDefs
    a = qdftemp.Name
    DoCmd.OutputTo acQuery, a, "Excel Workbook (*.xlsx)", _
    "C:\DATA\" & qdftemp.Name & ".xlsx", False, ""
    Debug.Print qdftemp.Name
    Next
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try specifying you're output type as: acSpreadsheetTypeExcel12

    MSDN Reference for help on OutputTo.

    Regards
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why not use DoCmd.TransferSpreadsheet

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hate to ask but could you edit my code to use the acSpreadsheetTypeExcel12 and a version with the TransferSpreadsheet methods.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try:
    DoCmd.OutputTo acOutputQuery, qdeftemp.name, acSpreadsheetTypeExcel12,"C:\DATA\" & qdftemp.Name & ".xlsx"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It errored out with the message that Object was missing

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Have you tried single stepping through the code to see where it is failing and the value of the variables at that point?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes I've been stepping through and I've gotten it to run by changing the line to
    DoCmd.OutputTo acOutputQuery, a, acSpreadsheetTypeExcel12, "C:\DATA\" & qdftemp.Name & ".xls"
    When I try the extension of "xlsx" I can't open the file.

    When I try the query with +65,000 records I still get an error saying it can't export that many lines. It will save the file but stops at 65,535 lines.
    The error message says I've selected more records than can be placed on the clipboard.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    .xls files can only have 65,535 rows! To get more than that you need to use the .xslx filetype. Why it won't work with that filetype I don't know. What version of Excel are you using?
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-01-25 at 11:48. Reason: Add Excel Limits Chart - Graphic
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using Excel 2007. When I use the xlsx extension the file will not open. When I rename the exported file to an xls extension it will open.

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts
    FIXED IT!!! This one works perfectly! just had to change some things around and used xlsb extension.

    THANK YOU!!!!!

    DoCmd.TransferSpreadsheet acQuery, acSpreadsheetTypeExcel12, a, "C:\DATA\" & qdftemp.Name & ".xlsb", False, ""

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Here's an interesting article on the .xlsb file format. I just had to research it after your post. Glad you have it working.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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