Проблемы в Excel 2013 при использовании диапазонов, выходящих за пределы строки 65536

Я пытаюсь выполнить запрос ADODB для именованного диапазона в книге Excel 2013.

Мой код выглядит следующим образом:

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

Если диапазон HighRange выходит за пределы строки 65536 (например, A65527:B65537), я получаю сообщение об ошибке введите здесь описание изображения

Если я удалю достаточно строк, чтобы опустить диапазон ниже строки 65536, код сработает.

Код также работает, если я заставляю книгу работать только для чтения (и гарантирую, что ни у кого другого не будет открытой версии, не предназначенной только для чтения).

Я что-то не так делаю или это ошибка в Excel 2013?

(Проблема существует как в 32-разрядной, так и в 64-разрядной версиях. Также существует в Excel 2016.)


excel vba adodb
person YowE3K    schedule 29.07.2016    source источник
comment
Я собираюсь предположить, что вы нашли экземпляр с MSoft, который забыл изменить int на long в своем коде движка MS Access db... Звучит как ошибка для меня. Попробовать в XL2016?   -  person David Colwell    schedule 29.07.2016
comment
К сожалению, это рабочая ситуация, поэтому мы просто переходим на Excel 2013. (Я не думаю, что у нас была проблема в Excel 2010.)   -  person YowE3K    schedule 29.07.2016
comment
forum.chandoo.org/threads/   -  person Tim Williams    schedule 29.07.2016
comment
Определенно кажется, что это проблема int против long, поскольку 65 535 является самым большим 16-битным целым числом без знака.   -  person Alexis Olson    schedule 29.07.2016
comment
Прочитав ссылку, опубликованную @TimWilliams, я подумал, что нашел обходной путь — скопировав диапазон на новый рабочий лист (убедившись, что целевая область начинается в строке 1), активировав этот рабочий лист, выполнив запрос к данным этого рабочего листа (без используя имя диапазона), а затем удаляя временный рабочий лист, но он не работает надежно, поскольку теряет информацию из некоторых строк, поэтому я вернулся к поиску предложений по ответам.   -  person YowE3K    schedule 29.07.2016
comment
Вы проверили, сохранен ли файл в правильном формате (xlsx, xlsm)? Вряд ли это будет проблемой, но стоит попробовать.   -  person Mister 832    schedule 29.07.2016
comment
@ Mister832 - Файл представляет собой файл xlsm. Я заметил, что расширенные свойства могут использовать макрос Excel 12.0 или Excel 12.0 xlm, а также Excel 12.0, который я использую, но ни один из них не решает проблему.   -  person YowE3K    schedule 29.07.2016
comment
@DavidColwell Я только что купил себе личную копию Excel 2016 - протестировал там код, и проблема осталась.   -  person YowE3K    schedule 13.06.2017

Ответы (2)


arrow_upward
1
arrow_downward

Я не смог найти фактический ответ на свою проблему, поэтому лучший обходной путь, который я мог придумать, - это создать дополнительную книгу, скопировать мой диапазон на лист в этой книге (начиная с ячейки A1), сохранить эту книгу, а затем использовать эту книгу/лист в качестве источника запроса.

(Первоначально я думал, что смогу просто создать временный лист в существующей книге, т. е. не создавая временную книгу, но проблемы возникают, если у пользователя активны два экземпляра Excel — событие Connection.Open повторно открывает книгу в первый экземпляр Excel, хотя мы запускаем макросы во втором экземпляре, и поэтому в повторно открытой книге нет фиктивного листа. И я не хочу сохранять копию существующей книги с помощью фиктивный лист в нем.)

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

Я бы все же предпочел более надежное решение этой проблемы, поэтому хотел бы, чтобы кто-то еще придумал другой ответ.

person YowE3K    schedule 02.08.2016

arrow_upward
0
arrow_downward

Что ж, если это все еще открытый вопрос, у меня была та же проблема, и я решил ее, не указав никаких номеров строк в запрашиваемом диапазоне. Пример:

Я пытался запросить [SheetName$A1:W100000], что дало мне ту же ошибку, что и у вас. Затем я указал диапазон как [SheetName$A:W], и ​​это охрененно!

Надеюсь это поможет!

person Rascio    schedule 28.03.2018