Excel VBA操作Range时出现Type Mismatch（类型不匹配）错误求助

核心错误原因 #

• 连接字符串错误：你把VBA的Range对象MyData直接拼进了OLEDB连接字符串，而OLEDB连接需要的是工作簿的完整路径，不是Range对象，这直接触发类型不匹配错误。
• SQL语句无效：OLEDB无法识别VBA中定义的MyData变量，必须使用Excel OLEDB支持的区域引用格式（如[工作表名$起始单元格:结束单元格]）或者命名范围。
• Data工作表行定位错误：你用原工作表的vLastRow来定位Data表的粘贴位置，会导致数据覆盖或错位，需单独计算Data表的最后一行。

修正后的完整代码 #

Sub ConsolidateData()
    Dim MyFileName As String
    Dim WrkShtCnt As Integer, ws As Integer
    Dim WrkShtName As String
    Dim vStartDate As String, vEndDate As String, vExtra As String, vStr As String
    Dim vLastRow As Long, dataLastRow As Long
    Dim MySheet As Worksheet
    Dim dBcn As New ADODB.Connection
    Dim dbRS As New ADODB.Recordset
    Dim dBConn As String, vsql As String
    Dim FirstTime As Boolean
    
    FirstTime = True
    MyFileName = ActiveWorkbook.FullName ' 获取工作簿完整路径，而非仅名称
    WrkShtCnt = Worksheets.Count
    
    For ws = 1 To WrkShtCnt ' 改用For循环更简洁直观
        DoEvents
        WrkShtName = Trim(Worksheets(ws).Name)
        
        If UCase(WrkShtName) = "NOTES" Or UCase(WrkShtName) = "DATA" Then
            ' 跳过不需要提取数据的工作表
        Else
            With Worksheets(ws)
                vStartDate = Format(.Cells(2, 1), "mm/dd/yyyy")
                vEndDate = Format(.Cells(3, 1), "mm/dd/yyyy")
                vExtra = UCase(Trim(.Cells(4, 1)))
                
                vStr = vStartDate & " - " & vEndDate
                If Len(vExtra) > 0 Then vStr = vStr & " " & vExtra
                
                ' 获取当前工作表数据区域的最后一行
                vLastRow = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
                
                ' 转换为OLEDB可识别的区域引用格式
                Dim rangeRef As String
                rangeRef = "[" & .Name & "$" & .Cells(11, 13).Address(False, False) & ":" & .Cells(vLastRow, 30).Address(False, False) & "]"
            End With
            
            ' 构建正确的OLEDB连接字符串
            dBConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyFileName & _
                     ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
            
            dBcn.Open dBConn
            
            ' 编写合法的SQL查询语句
            vsql = "SELECT * FROM " & rangeRef
            
            dbRS.Open vsql, dBcn ' 此语句不再触发类型不匹配错误
            
            ' 计算Data工作表的目标粘贴行，避免数据覆盖
            With Worksheets("Data")
                If FirstTime Then
                    dataLastRow = 2
                    FirstTime = False
                Else
                    dataLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                End If
                .Cells(dataLastRow, 1).CopyFromRecordset dbRS
            End With
            
            ' 清理资源
            dbRS.Close
            dBcn.Close
        End If
    Next ws
    
    ' 释放对象变量
    Set dBcn = Nothing
    Set dbRS = Nothing
    Set MySheet = Nothing
End Sub

关键修改说明 #

1. 连接字符串修正：用ActiveWorkbook.FullName获取工作簿完整路径，替换原代码中错误的Range对象引用，满足OLEDB连接的参数要求。
2. 区域引用转换：将VBA的Range对象转换为[工作表名$A1:B10]格式，确保SQL语句能被OLEDB引擎正确解析。
3. Data表行定位修正：通过.Cells(.Rows.Count, 1).End(xlUp).Row + 1计算Data表的最后一行，保证数据追加在已有数据之后。
4. 代码优化：改用For循环替代Do Until，用With语句减少重复引用，提升代码可读性与执行效率。
5. 资源管理：在过程结束时统一释放所有对象变量，避免内存泄漏。

额外建议 #

• 如果数据区域没有表头，将连接字符串中的HDR=Yes改为HDR=No，OLEDB会自动以F1、F2等作为列名。
• 确保安装Microsoft Access Database Engine 2010 Redistributable，避免因ACE.OLEDB.12.0驱动缺失导致连接失败。

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