求修改VBA代码以动态更新数据透视表的CommandText数据源范围

需求说明 #

需要实现一段VBA代码，完成以下操作：

• 自动识别「Billing Analysis」工作表的最后一行有效数据
• 修改「查询与连接」中对应连接的「命令文本」，将数据源范围适配至最后一行
• 刷新指定的数据透视表

原代码在执行conn.OLEDBConnection.CommandText = cmdText时触发运行时错误1004，以下是修正后的完整代码：

Sub UpdatePivotSourceRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cmdText As String
    Dim conn As WorkbookConnection
    Dim pt As PivotTable
    
    ' 定位数据源工作表
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Billing Analysis")
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "未找到「Billing Analysis」工作表！", vbExclamation
        Exit Sub
    End If
    
    ' 获取数据源最后一行（以A列为基准）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then ' 至少需要表头+1行有效数据
        MsgBox "数据源无有效数据！", vbExclamation
        Exit Sub
    End If
    
    ' 构建符合OLEDB规范的命令文本格式
    cmdText = "SELECT * FROM `Billing Analysis$A1:AA" & lastRow & "`"
    
    ' 定位并更新目标连接
    Dim connFound As Boolean
    connFound = False
    For Each conn In ThisWorkbook.Connections
        ' 通过关键字匹配连接，避免硬编码过长的默认连接名
        If InStr(conn.Name, "Billing Analysis") > 0 And conn.Type = xlConnectionTypeOLEDB Then
            conn.OLEDBConnection.CommandText = cmdText
            ' 重置连接字符串，确保指向当前工作簿
            conn.OLEDBConnection.ConnectionString = _
                "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            conn.Refresh
            connFound = True
            Exit For
        End If
    Next conn
    
    If Not connFound Then
        MsgBox "未找到关联「Billing Analysis」的OLEDB连接！", vbExclamation
        Exit Sub
    End If
    
    ' 刷新指定数据透视表
    On Error Resume Next
    Set pt = ThisWorkbook.Sheets("No. of Billings-by Service").PivotTables("PivotTable5")
    On Error GoTo 0
    If pt Is Nothing Then
        MsgBox "未找到指定的数据透视表！", vbExclamation
        Exit Sub
    End If
    
    pt.RefreshTable
    MsgBox "数据透视表已更新数据源范围并完成刷新！", vbInformation
End Sub

关键修正点 #

• 格式规范适配：OLEDB连接的「命令文本」需要遵循SQL查询格式，而非直接写单元格范围，格式为SELECT * FROM 工作表名$起始单元格:结束单元格``
• 容错逻辑补充：增加了工作表、连接、透视表的存在性判断，避免因对象缺失触发错误
• 连接匹配优化：通过关键字匹配连接名称，避免硬编码随初始范围变化的默认连接名
• 连接字符串重置：重置连接字符串确保指向当前工作簿，避免因文件路径变更导致的连接失效

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