Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Won't output to Excel (XP)

    I have a query which contains 21,211 records and have attempted to output it to Excel for someone to manipulate. Access/Excel keeps telling there are too many rows to export and then Excel is telling me the error below at the same time. Any ideas why?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Won't output to Excel (XP)

    Access probably exports to an olde Excel format from the time a sheet only had 16,384 rows. Try splitting the query into two parts, each with fewer than 16,384 rows.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Won't output to Excel (XP)

    Thanks Hans

    That is a new one on me but thanks for a great top tip. To get around this problem I eventually just exported this to a txt file and impoted the txt file into Excel. I felt that there was an equal amount of work involved and it was a one off job.

    Your <post#=482244>post 482244</post#> was the inspiration <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Won't output to Excel (XP)

    This may not cure your problem but Access does have an issue with exporting to Excel with a lot of defined elements. If in Access's view, there are over 256 defined elements (could be re-naming a column, calculated fields, ...) Access will not export the data. If you have that query do a make-table query and then export that table, you don't have that issue any more.

    The versions of Access and Excel that I have been using don't start to choke until you hit 65,000 rows of data, not a measly 21,211! :-)~

    HTH!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Won't output to Excel (XP)

    Prior to Office 97, Excel would only allow up to 16K rows - with 97 and later, it supports 64K rows. Chances are the method being used is defaulting to the older Excel version - you can force it by specifying Access 97 and later in the SaveAs when you do it manually.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't output to Excel (XP)

    This may or may not help, but I did come across this problem some time ago when using Docmd.TransferSpreadsheet
    & I hit that limitation.

    To get around this I used the following code :

    strSQL = "SELECT tblAPMTrans.* FROM tblAPMTrans;"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rst
    .MoveLast
    .MoveFirst
    lngCount = .RecordCount
    intX = .Fields.Count
    End With

    '----------------------------------------------------------
    ' Manually build the variant array to store the contents of
    ' the recordset. GetRows would be simpler to use but pasting
    ' the array into Excel will not work if there are more than
    ' 255 rows. This is because the Transpose command needed will
    ' not work for > 255 columns - Excel's maximum
    '----------------------------------------------------------
    ReDim avarData(lngCount, intX)
    For lngRowCount = 0 To lngCount - 1
    For intColCount = 0 To intX - 1
    strDataType = TypeName(rst.Fields(intColCount).Value)
    Select Case strDataType
    Case "Currency"
    avarData(lngRowCount, intColCount) = Format(rst.Fields(intColCount).Value, "

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Won't output to Excel (XP)

    Thanks Nick

    I shall certainly give that a try <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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