excel - VBA 删除连接， "Assignment to constant not permitted"错误

Question

我正在尝试使用 VBA 删除重复的查询。我使用了一个类似的宏来关闭特定连接的全部刷新刷新，但我似乎在将相同的概念应用于删除时遇到了问题。

Sub DeleteConnections()
Dim cn As WorkbookConnection
Dim qr As WorkbookQuery
    For Each cn In ActiveWorkbook.Connections
        cn.Delete = CBool(cn.Name Like "BPTable(*" Or cn.Name Like "BPResourceChart(*" Or cn.Name Like "BPFlowSteps(*" _
            Or cn.Name Like "BPDayChart(*" Or cn.Name Like "BPResource(*" Or cn.Name Like "BPTable2(*" _
            Or cn.Name Like "BPHistoric(*" Or cn.Name Like "BPHistoryHours(*")
    Next
    For Each qr In ActiveWorkbook.Queries
        qr.Delete = CBool(qr.Name Like "BPTable(*" Or qr.Name Like "BPResourceChart(*" Or qr.Name Like "BPFlowSteps(*" _
            Or qr.Name Like "BPDayChart(*" Or qr.Name Like "BPResource(*" Or qr.Name Like "BPTable2(*" _
            Or qr.Name Like "BPHistoric(*" Or qr.Name Like "BPHistoryHours(*")
    Next
End Sub

全部刷新时停止刷新:

Sub RefreshAllBlock()
 Dim conn As Object
    For Each conn In ActiveWorkbook.Connections
        conn.RefreshWithRefreshAll = Not CBool(conn.Name Like "*BP*" Or conn.Name Like "*ProductionSupportTickets*")
    Next
End Sub

完整的代码解决方案感谢 BigBen:

Sub DeleteConnections()
Dim cn As WorkbookConnection
Dim qr As WorkbookQuery
    For Each cn In ActiveWorkbook.Connections
        If cn.Name Like "*BPTable (*" Or cn.Name Like "*BPResourceChart (*" Or cn.Name Like "*BPFlowSteps (*" _
            Or cn.Name Like "*BPDayChart (*" Or cn.Name Like "*BPResource (*" Or cn.Name Like "*BPTable2 (*" _
            Or cn.Name Like "*BPHistoric (*" Or cn.Name Like "*BPHistoryHours (*" Then
        cn.Delete
        End If
        Next
    For Each qr In ActiveWorkbook.Queries
        If qr.Name Like "BPTable (*" Or qr.Name Like "BPResourceChart (*" Or qr.Name Like "BPFlowSteps (*" _
            Or qr.Name Like "BPDayChart (*" Or qr.Name Like "BPResource (*" Or qr.Name Like "BPTable2 (*" _
            Or qr.Name Like "BPHistoric (*" Or qr.Name Like "BPHistoryHours (*" Then
        qr.Delete
        End If
        Next
End Sub

Answer 1

你混淆了方法和属性。
WorkbookConnection.Delete 和 WorkbookQuery.Delete 是被调用的方法；不能像您尝试的那样分配 bool 值。
相比之下， WorkbookConnection.RefreshWithRefreshAll 是可以更改的属性。1
改变

cn.Delete = ....

至

If cn.Name Like "BPTable(" Or cn.Name Like "BPResourceChart(*" Or cn.Name Like "BPFlowSteps(" _
            Or cn.Name Like "BPDayChart(*" Or cn.Name Like "BPResource(*" Or cn.Name Like "BPTable2(*" _
            Or cn.Name Like "BPHistoric(*" Or cn.Name Like "BPHistoryHours(*)" Then
    cn.Delete
End If

和类似的查询。
1 请注意，属性可以是只读的。如文档中所述， RefreshWithRefreshAll是读/写。