Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limits on Export to Excel (Access 2000)

    Where can you set the limits for export to Excel. Currently as soon as have more than 20000 records I can't export it or "Analyse with Excel". BUT it I select all the records and do a copy and paste into Excel it works.

    Thanks for any ideas

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Limits on Export to Excel (Access 2000)

    The limit is not on the access side but on the excel side. Which version of excel are you using? Try searching on exporting to excel in both the access and excel forums for more details.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Limits on Export to Excel (Access 2000)

    This is because "Analyze with MS Excel" command exports data to Excel in primitive Excel 5.0 format. See MSKB 201589:

    ACC2000: "Too Many Rows" Error Using "Analyze It with MS Excel"

    Reason for limitation: "For backward compatibility, Microsoft Access defaults to the Excel 5.0 specification, which is limited to 16,384 rows." Article provides possible solutions. I'd recommend second option:

    "In Access, click Export on the File menu, and then export to Microsoft Excel 97-2000 format. Make sure that the Save formatted check box in the Export Table dialog box in Access is not selected. Then open the file in Excel." I tested this approach with table with 65536 records (the maximum number of rows in an XL worksheet) and all records were successfully exported (except for record no 65536, which wasn't exported because the field names are exported as column headers in the Excel file).

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limits on Export to Excel (Access 2000)

    Thanks MarkD

Posting Permissions

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