Office 365 Vs. Excel 2010,2013 adodb recordset limit

[mannaman]

Hello, I have a workbook which returns over 220,000+ records from an Oracle DB. It works great in Excel 2010 & 2013 but when I shared it with a co-worker running 'Office 365 Pro' it only returned 65,535 records. I am using ADODB.Recordset to hold the data and output it to Range("A2") using CopyFromRecordset. There is no error but it won't go past 65K rows even though 365 has over 1M rows. Any ideas or suggestions?

    Dim FldCount As Long, NbrFormat As String
    ' Copy field names to the first row of the worksheet
    FldCount = RS.Fields.Count
    For f = 1 To FldCount
        NbrFormat = ""
        Select Case LCase(Left(RS.Fields(f - 1).Name, 1)) 'NbrFormat
            Case "s": NbrFormat = "@"
            Case "n": NbrFormat = "_(* #,##0.00_)[Black];_(* (#,##0.00);_(* ""-""??_)[Red];_(@_)" ' "#,##0.00[Green]"
            Case "d": NbrFormat = "mm/dd/yyyy"
            Case Else: NbrFormat = "@"
        End Select
        XLWS.Columns(f).NumberFormat = NbrFormat
        With XLWS.Cells(1, f)
            .Value = Mid(RS.Fields(f - 1).Name, 2)
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .Interior.Color = rgbAquamarine
        End With
    Next
    XLWS.Cells(2, 1).CopyFromRecordset RS

Thanks for any ideas or suggestions,
Tim

 

[SpillerBD]

Is the Co-Worker using a XLS workbook instead of an XLSX (or XLSM) workbook?

 

[mannaman]

Nope, we tried it on another desktop running 'Office 365 Pro'. There are over a million rows available. The oldest version we have is Office 2010 on anyone's computer. It has to be some component of Office 365. I checked all the Tools > References and it is running the same ones as Excel 2013.

Thanks,
Tim

 

[MarkAMcCain]

Looks like it might be a known bug in 365:

Do a Google search for "excel 365 database only returns 65K rows" and it pops right up.

 

[mannaman]

Looks like it might be a known bug in 365:

Do a Google search for "excel 365 database only returns 65K rows" and it pops right up.

Thanks Mark. I didn't find a resolution for users running Office 365 other than possibly putting the data onto separate sheets. That isn't an option at this point using CopyFromRecordset option which runs in seconds vs. reading and counting row by row. If you have any ideas I am open to suggestions. Just to repeat for future links this only happens in "Office 365" and it is up to date.

Thanks again,
Tim