Incomplete Recordset from ADODB Connection

Question

I am trying to retrieve data from a worksheet that contains 80,000+ rows and print those values to a worksheet, but when I create a recordset and view its recordcount it only contains 16,492 records.

I am new to ADODB connections, so I am stumped as to what the problem is.

Here is my code:

Sub testing()
    On Error Resume Next

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Dim objCon As New ADODB.Connection
    Dim recSet As ADODB.Recordset

    Set objCon = CreateObject("ADODB.Connection")
    Set recSet = CreateObject("ADODB.Recordset")

    objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\nschneider\Desktop\Data Pulls_FY08 to Present_Companies 10, 20, 30, 40.xlsx;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

    recSet.Open "Select * FROM [Data$]", _
        objCon, adOpenStatic, adLockOptimistic, adCmdText

    If Not recSet.EOF Then
        Sheets(2).Range("A2").CopyFromRecordset recSet
    End If

    '    Do Until objRecordset.EOF
    '        Wscript.Echo objRecordset.Fields.Item("Name"), _
    '            objRecordset.Fields.Item("Number")
    '        objRecordset.MoveNext
    '
    '    Loop
End Sub

I've commented out the Loop as that was something I saw from a previous forum. Not sure if that is what I need.

Answer 1

You're using the connection string for Excel 97-2003 .xls file (specifically the part which says "Excel 8.0"). Excel sheets were limited to 65K rows back then. The connection string is then being applied to an Excel 2007-onwards .xlsx file where the maximum number of rows per sheet is over 1 million.

The older connection string works with newer .xlsx files but I've had issues when the row count on a sheet exceeds the old 65K maximum. Update the connection string to the correct one for a .xlsx file and see if that helps:

objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\nschneider\Desktop\Data Pulls_FY08 to Present_Companies 10, 20, 30, 40.xlsx;" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"

(I also removed an unnecessary ; after "HDR=Yes")