使用范围超出行65536的范围时Excel 2013中的问题 [英] Problems in Excel 2013 when using ranges that extend beyond row 65536

问题描述

我正在尝试在Excel 2013工作簿中的命名范围上执行ADODB查询。

我的代码如下：

  Option Explicit 
 Sub SQL_Extract（）
 Dim objConnection as ADODB.Connection 
 Dim objRecordset As ADODB.Recordset 
设置objConnection = CreateObject（ ADODB.Connection）'数据集查询对象
设置objRecordset = CreateObject（ ADODB.Recordset）'由查询
 
 objConnection.ConnectionString =创建的新数据集Provider = Microsoft.ACE.OLEDB.12.0; & _ 
数据源=& ThisWorkbook.FullName& ; & _ 
扩展属性= Excel 12.0; HDR =是; IMEX = 1; 
 objConnection.Open 
 
 objRecordset.Open SELECT * FROM [HighRange]，objConnection，adOpenStatic，adLockOptimistic，adCmdText 
 
如果不是objRecordset.EOF，则
 ActiveSheet.Cells（1，1）.CopyFromRecordset objRecordset 
如果
 
 objRecordset.Close 
 objConnection.Close 
 End Sub 
  

如果范围 HighRange 超出行65536（例如A65527：B65537），收到错误消息

如果我删除了足够多的行以将范围降至第65536行以下，则代码

如果我强制工作簿为只读（并确保没有其他人打开非只读版本），该代码也将起作用。

这是我做错了，还是Excel 2013中的错误？

（Probl em存在于32位和64位版本中。 Excel 2016中也存在。）

解决方案

我无法找到问题的实际答案，因此最好我可以想到的解决方法是创建一个额外的工作簿，将我的范围复制到该工作簿中的工作表（从单元格A1开始），保存该工作簿，然后将该工作簿/工作表用作查询的源。 / p>

（我原本以为可以在现有工作簿中创建一个临时工作表，而无需创建一个临时工作簿，但是如果用户有两个实例，则会出现问题Excel处于活动状态-即使我们在第二个实例中运行宏，Connection.Open事件也会在第一个Excel实例中重新打开工作簿，因此重新打开的工作簿中没有虚拟工作表。我不想保存其中包含虚拟工作表的现有工作簿的副本。）

  Sub SQL_Extract_Fudged（）
 Dim objConnection作为ADODB.Conn 
 Dim objRecordset作为ADODB.Recordset 
 Dim wsOrig作为工作表
 Dim wbTemp作为工作簿
 Dim wbTempName作为字符串
 Dim wsTemp作为工作表
 
设置wsOrig = ActiveSheet 
 
'为临时工作簿
生成文件名wbTempName = Environ $（ TEMP）&  \TempADODBFudge_& Format（Now（）， yyyymmdd_hhmmss）&  .xlsx 
'创建临时工作簿
设置wbTemp = Workbooks.Add 
'使用第一张纸作为我们要使用的范围的临时副本的位置
设置wsTemp = wbTemp.Worksheets（1）
 wsTemp.Name = TempADODBFudge 
'将查询范围复制到临时工作表
 wsOrig.Range（ HighRange）。Copy目标：= wsTemp。 Range（ A1）
'保存并关闭临时工作簿
 wbTemp.SaveAs wbTempName 
 wbTemp.Close False 
'摆脱对临时工作簿
的引用Set wsTemp = Nothing 
 Set wbTemp = Nothing 
 
'创建连接和记录集对象
 Set objConnection = CreateObject（ ADODB.Connection）
 Set objRecordset = CreateObject（  ADODB.Recordset）
 
'创建指向临时工作簿的连接字符串
 objConnection.ConnectionString = Provider = Microsoft.ACE.OLEDB.12.0; & _ 
数据源=& wbTempName& ; & _ 
扩展属性= Excel 12.0; HDR =是; IMEX = 1; 
 objConnection.Open 
 
'对整个临时工作表执行查询
 objRecordset.Open SELECT * FROM [TempADODBFudge $]，objConnection，adOpenStatic，adLockOptimistic，adCmdText 
 
'复制输出（对于本示例，我只是将其复制回原始工作表）
如果不是objRecordset.EOF然后
 wsOrig.Cells（1，1）.CopyFromRecordset objRecordset 
 End If 
 
'关闭连接
 objRecordset.Close 
 objConnection.Close 
 
'摆脱临时工作簿
错误恢复下一个
错误终止wbTempName 
转到0 
 
结束子
  

对于该问题，我仍然希望使用更可靠的解决方案，因此希望其他人提出另一个答案。

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.)

解决方案

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.

这篇关于使用范围超出行65536的范围时Excel 2013中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！