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是被调用的方法;不能像你尝试的那样分配布尔值。

相比之下，WorkbookConnection.RefreshWithRefreshAll是可以更改的属性。¹

改变

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

查询也是如此。

¹请注意，属性可以是只读的。如文档中所述，RefreshWithRefreshAll是读/写。