如何在ADODB中读取指定范围数据并确保读取至数据末尾行？

我有两个工作表，想用ADODB建立数据库连接，通过SELECT语句把「Data」工作表的数据汇总后粘贴到「Summary」工作表里。当FROM子句不指定具体行范围（比如[Data$C11:CR]）时查询能正常运行，但因为表头在C11到CR区域，必须限定范围，可指定[Data$C11:CR200000]后查询就失效了。怎么才能让记录集正确读取到数据的最后一行？

原测试代码 #

Option Explicit

Sub test()

Dim Conn, Rcs As Object, sql As String

Set Conn = CreateObject("ADODB.Connection")
    With Conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
    .Open
     End With
     
     ' 此语句正常运行：未指定Data工作表的行范围
     sql = "TRANSFORM SUM(T1.[Grand Total]) " & _
           "SELECT T1.[Region],T1.[Sub Region],T1.[OU] " & _
           ",T1.[Customer Number],T1.[Customer Name] " & _
           "FROM [Data$C11:CR] T1 " & _
           "WHERE NOT T1.[Region] IN ('Bottom') " & _
           "GROUP BY T1.[Customer Number] " & _
           "PIVOT T1.[Aging Buckets]"
     
     
    ' 此语句失效：指定了固定范围[Data$C11:CR200000]
    sql = "TRANSFORM SUM(T1.[Grand Total]) " & _
           "SELECT T1.[Region],T1.[Sub Region],T1.[OU] " & _
           ",T1.[Customer Number],T1.[Customer Name] " & _
           "FROM [Data$C11:CR200000] T1 " & _
           "WHERE NOT T1.[Region] IN ('Bottom') " & _
           "GROUP BY T1.[Customer Number] " & _
           "PIVOT T1.[Aging Buckets]"
           

     Set Rcs = Conn.Execute(sql)
 
       If Not Rcs.BOF And Not Rcs.EOF Then
        Sheets("Summary").Cells(5, 2).CopyFromRecordset Rcs
            End If

   Rcs.Close
   Conn.Close
Set Rcs = Nothing
Set Conn = Nothing

End Sub

1. 动态获取实际数据最后行，拼接SQL范围 #

不要硬写固定行号，先找到「Data」工作表中关键数据列（比如C列，对应Region字段）的实际最后一行，再动态生成SQL里的范围：

Option Explicit

Sub test()

Dim Conn, Rcs As Object, sql As String
Dim lastRow As Long

' 获取Data工作表C列的实际最后一行
lastRow = Sheets("Data").Cells(Sheets("Data").Rows.Count, "C").End(xlUp).Row

Set Conn = CreateObject("ADODB.Connection")
    With Conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
    .Open
     End With
     
     ' 动态拼接FROM子句的范围
     sql = "TRANSFORM SUM(T1.[Grand Total]) " & _
           "SELECT T1.[Region],T1.[Sub Region],T1.[OU] " & _
           ",T1.[Customer Number],T1.[Customer Name] " & _
           "FROM [Data$C11:CR" & lastRow & "] T1 " & _
           "WHERE NOT T1.[Region] IN ('Bottom') " & _
           "GROUP BY T1.[Customer Number] " & _
           "PIVOT T1.[Aging Buckets]"
           
     Set Rcs = Conn.Execute(sql)
 
       If Not Rcs.BOF And Not Rcs.EOF Then
        Sheets("Summary").Cells(5, 2).CopyFromRecordset Rcs
            End If

   Rcs.Close
   Conn.Close
Set Rcs = Nothing
Set Conn = Nothing

End Sub

2. 排查固定范围失效的核心原因 #

• 当指定到CR200000这种超大范围时，OLEDB会将范围内所有空行都视为有效数据行，大量空数据会干扰聚合逻辑，甚至因为数据类型识别冲突导致查询失败。
• 若数据列存在混合数据类型（比如同一列既有文本又有数字），可将连接字符串中的IMEX=0改为IMEX=1，强制以文本类型读取所有数据，避免类型不匹配问题：

.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"

3. 使用动态命名范围替代硬编码范围 #

在「Data」工作表中创建动态命名范围，让范围自动跟随数据行变化：

1. 打开Excel的「公式」选项卡，点击「定义名称」。
2. 名称设为DataRange，引用位置输入公式：

   =OFFSET(Data!$C$11,0,0,COUNTA(Data!$C:$C)-10,COLUMNS(Data!$C:$CR))
   

   （公式中-10是因为表头从C11开始，前面10行不属于数据区域，可根据实际情况调整）
3. 修改SQL语句直接使用该命名范围：

   sql = "TRANSFORM SUM(T1.[Grand Total]) " & _
         "SELECT T1.[Region],T1.[Sub Region],T1.[OU] " & _
         ",T1.[Customer Number],T1.[Customer Name] " & _
         "FROM DataRange T1 " & _
         "WHERE NOT T1.[Region] IN ('Bottom') " & _
         "GROUP BY T1.[Customer Number] " & _
         "PIVOT T1.[Aging Buckets]"
   

内容的提问来源于stack exchange，提问作者Mo007