Problems in Excel 2013 when using ranges that extend beyond row 65536

Question

I am trying to perform an ADODB query on a named range in an Excel 2013 workbook.

My code is as follows:

Option Explicit
Sub SQL_Extract()
    Dim objConnection           As ADODB.Connection
    Dim objRecordset            As ADODB.Recordset
    Set objConnection = CreateObject("ADODB.Connection")        ' dataset query object
    Set objRecordset = CreateObject("ADODB.Recordset")          ' new dataset created by the query

    objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                     "Data Source=" & ThisWorkbook.FullName & ";" & _
                                     "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    objConnection.Open

    objRecordset.Open "SELECT * FROM [HighRange]", objConnection, adOpenStatic, adLockOptimistic, adCmdText

    If Not objRecordset.EOF Then
        ActiveSheet.Cells(1, 1).CopyFromRecordset objRecordset
    End If

    objRecordset.Close
    objConnection.Close
End Sub

If the range HighRange extends beyond row 65536 (e.g. A65527:B65537) I get an error message

If I remove enough rows to drop the range below row 65536, the code works.

The code also works if I force the workbook to be read-only (and ensure that no-one else has a non-read-only version open).

Is this something I am doing wrong, or is this a bug in Excel 2013?

(Problem exists in both 32-bit and 64-bit versions. Also exists in Excel 2016.)

Answer 1

I haven't been able to find an actual answer to my problem, so the best work-around I could come up with is to create an extra workbook, copy my range to a sheet in that workbook (starting at cell A1), save that workbook, and then use that workbook/worksheet as the source of the query.

(I originally thought I could get away with just creating a temporary worksheet in the existing workbook, i.e. without creating a temporary workbook, but problems occur if the user has two instances of Excel active - the Connection.Open event re-opens the workbook in the first instance of Excel, even though we are running the macros in the second instance, and therefore the re-opened workbook doesn't have the dummy worksheet in it. And I don't want to save a copy of the existing workbook with a dummy sheet in it.)

Sub SQL_Extract_Fudged()
    Dim objConnection           As ADODB.Connection
    Dim objRecordset            As ADODB.Recordset
    Dim wsOrig As Worksheet
    Dim wbTemp As Workbook
    Dim wbTempName As String
    Dim wsTemp As Worksheet

    Set wsOrig = ActiveSheet

    'Generate a filename for the temporary workbook
    wbTempName = Environ$("TEMP") & "\TempADODBFudge_" & Format(Now(), "yyyymmdd_hhmmss") & ".xlsx"
    'Create temporary workbook
    Set wbTemp = Workbooks.Add
    'Use first sheet as the place for the temporary copy of the range we want to use
    Set wsTemp = wbTemp.Worksheets(1)
    wsTemp.Name = "TempADODBFudge"
    'Copy the query range to the temporary worksheet
    wsOrig.Range("HighRange").Copy Destination:=wsTemp.Range("A1")
    'Save and close the temporary workbook
    wbTemp.SaveAs wbTempName
    wbTemp.Close False
    'Get rid of references to the temporary workbook
    Set wsTemp = Nothing
    Set wbTemp = Nothing

    'Create connection and recordset objects
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

    'Create the connection string pointing to the temporary workbook
    objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                     "Data Source=" & wbTempName & ";" & _
                                     "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    objConnection.Open

    'Perform the query against the entire temporary worksheet
    objRecordset.Open "SELECT * FROM [TempADODBFudge$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText

    'Copy output (for this example I am just copying back to the original sheet)
    If Not objRecordset.EOF Then
        wsOrig.Cells(1, 1).CopyFromRecordset objRecordset
    End If

    'Close connections
    objRecordset.Close
    objConnection.Close

    'Get rid of temporary workbook
    On Error Resume Next
    Kill wbTempName
    On Error GoTo 0

End Sub

I would still prefer a more robust solution to this problem, so would love someone else to come up with another answer.