Excel VBA宏刷新PivotTable6失败求助：代码问题排查

错误原因 #

你的代码中数据透视表刷新部分存在两处核心问题：

• 语法错误：高亮行For Each Pivot In Sheet.PivotTables("PivotTable6").PivotCache.Refresh完全不符合VBA语法。PivotCache.Refresh是执行刷新的方法，不返回可遍历集合，不能用于For Each循环的遍历对象。
• 逻辑混乱：嵌套循环写法无意义，既不需要遍历所有工作表（除非每个工作表都有PivotTable6），也不存在可遍历的"透视表集合"。

另外，原代码遗漏了保存文件的步骤，不符合你的需求。

修正后的代码 #

根据你的需求（打开文件→刷新连接→刷新指定透视表→保存），提供两种场景的修正代码：

场景1：仅刷新特定工作表中的PivotTable6 #

如果PivotTable6固定在某个工作表（比如名为"报表"的工作表），直接定位刷新即可：

Sub Daily_Sales_Cognos()
    'macro assigned to Daily Sales Button
    Dim wb As Workbook
    Dim myPath As String
    Dim FilePicker As FileDialog ' 声明变量类型，避免变体类型

    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

    'Cav Daily Sales Cognos
    With FilePicker
        .Title = "Select OTCD Op Daily Margin - W:\Reports\YYYY\MM"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1)
    End With

'In Case of Cancel
NextCode:
    If myPath = "" Then Exit Sub ' 原代码myPath = myPath是多余的

    Set wb = Workbooks.Open(Filename:=myPath)

    ' 刷新所有连接
    wb.RefreshAll
    ' 等待刷新完成（避免透视表还没拿到新数据就刷新）
    Application.Wait Now + TimeValue("00:00:02") ' 可根据数据量调整等待时间

    ' 刷新指定的PivotTable6（假设在"报表"工作表，可修改为实际表名）
    On Error Resume Next ' 防止表不存在报错
    wb.Worksheets("报表").PivotTables("PivotTable6").RefreshTable
    wb.Worksheets("报表").PivotTables("PivotTable6").PivotCache.Refresh ' 强制刷新缓存
    On Error GoTo 0

    ' 保存文件
    wb.Save
    ' 可选：关闭文件
    ' wb.Close SaveChanges:=False ' 因为已经Save过，这里可以False
End Sub

场景2：遍历所有工作表，刷新每个工作表中的PivotTable6 #

如果每个工作表都存在PivotTable6，可以用循环遍历：

Sub Daily_Sales_Cognos()
    'macro assigned to Daily Sales Button
    Dim wb As Workbook
    Dim myPath As String
    Dim FilePicker As FileDialog
    Dim ws As Worksheet ' 变量名避免用Sheet（VBA内置对象名）

    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

    'Cav Daily Sales Cognos
    With FilePicker
        .Title = "Select OTCD Op Daily Margin - W:\Reports\YYYY\MM"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1)
    End With

'In Case of Cancel
NextCode:
    If myPath = "" Then Exit Sub

    Set wb = Workbooks.Open(Filename:=myPath)

    ' 刷新所有连接
    wb.RefreshAll
    Application.Wait Now + TimeValue("00:00:02")

    ' 遍历所有工作表，刷新每个表中的PivotTable6
    For Each ws In wb.Worksheets
        On Error Resume Next
        ws.PivotTables("PivotTable6").RefreshTable
        ws.PivotTables("PivotTable6").PivotCache.Refresh
        On Error GoTo 0
    Next ws

    ' 保存文件
    wb.Save
End Sub

额外提示 #

• 变量命名尽量避免使用VBA内置对象名（比如原代码中的Sheet，改用ws更安全）。
• Application.Wait用于确保数据连接刷新完成后再处理透视表，如果数据量较大，可以适当延长等待时间。
• On Error Resume Next用于避免因某个工作表中不存在PivotTable6导致宏直接崩溃。

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