Office 365 Vs. Excel 2010,2013 adodb recordset limit

mannaman

New Member
Joined
Aug 15, 2016
Messages
3
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?

Code:
    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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
Is the Co-Worker using a XLS workbook instead of an XLSX (or XLSM) workbook?
 

mannaman

New Member
Joined
Aug 15, 2016
Messages
3
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

New Member
Joined
Jan 2, 2014
Messages
18
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.
 
Last edited:

mannaman

New Member
Joined
Aug 15, 2016
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top