excel - VBA 上的 400 条消息

Question

您好我正在尝试在同一代码中刷新特定的数据透视表和连接/查询/工作表。第一次运行时出现 400 错误。如果我再试一次，代码将运行并有效。我认为 Worksheets("Rejects_Dashboard) 行有问题。

Sub RefreshAllPivotTablesAtOnce()
Dim wb As Workbook:         Set wb = ThisWorkbook
Dim con As WorkbookConnection

    For Each con In wb.Connections
        If con.Name = "BPO_STATS_DB" Then con.Refresh
        Worksheets("Rejects_Dashboard").PivotTables("PivotTable2")
        .PivotCache.Refresh
Next con
End Sub

标签： excelvba

Answer 1

一些建议：

Sub RefreshAllPivotTablesAtOnce()
    Dim wb As Workbook:         Set wb = ThisWorkbook
    Dim con As WorkbookConnection

    'Refresh the folderContents query
    For Each con In wb.Connections
        If con.Name = "BPO_STATS_DB" Then 
            con.Refresh
            wb.Worksheets("Rejects_Dashboard").PivotTables( _
                            "PivotTable2").PivotCache.Refresh
             Exit For 'done at this point
        End If
    Next con
End Sub