ADODB Excel Connection cannot find a Worksheet

Question

I have a vba function which connects to the same workbook using an ADODB Connection. However, it only works for SOME worksheets. If I try to query a worksheet which does exist, it returns an error. However, other worksheets work fine. My code is below:

Function runExcelQuery(wk As Workbook, strQuery As String) As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim connectionString As String
    'Setup the connection string for accessing Excel
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & wk.FullName & _
        ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
    cnn.Open connectionString
    cnn.CommandTimeout = 0
    rst.Open strQuery, cnn
    Set runExcelQuery = rst
Exit Function

score 2 · Accepted Answer · answered Jul 30 '18 at 20:54

Okay, after a lot of digging around I discovered the problem was actually the number of rows on the sheet. If it is greater than 65k rows then the query must be written differently in order to work.

This Post: Excel as database - query more than 65536 rows? should give more details.

ADODB Excel Connection cannot find a Worksheet

1 Answers1