CODEWITHSUNDEEP
excelvbapowerqueryexcel-2016
JarvisButler290
JarvisButler290

Reputation: 83

VBA Deleting Connections, "Assignment to constant not permitted" Error

I am attempting to delete duplicated queries using VBA. I used a similar macro to turn off the refresh on refresh all for specific connections but I seem to have issues with apply the same concept to deleting.

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

Stop Refresh on Refresh All:

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

Full Code Solution Thanks to 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

Upvotes: 1

Views: 216

Answers (1)

BigBen
BigBen

Reputation: 50008

You're confusing methods and properties.

WorkbookConnection.Delete and WorkbookQuery.Delete are methods that are called; one cannot assign a boolean like you're attempting to.

By contrast, WorkbookConnection.RefreshWithRefreshAll is a property that can be changed.1

Change

cn.Delete = ....

to

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

and similarly for the queries.

1 Note that properties can be read-only. As noted in the docs, RefreshWithRefreshAll is Read/Write.

Upvotes: 2

Related Questions