VBA to delete Power Query Connections or Folder

[JerryDNYC]

I have a master workbook with 10 Power Query connections. That workbook is a Read-Only file, and has a macro that saves the workbook with a different file name.

Once the file has been saved I want to delete all the queries. I've tried using

  ActiveWorkbook.Connections("Query - Report").Delete

but it's not deleting the queries.  Is there something else that needs to be done before being able to delete a query?

Incidentally, all 10 queries are contained within a query folder named Reports. I am able to right click on the folder and delete all the queries that way. Unfortunately the VBA recorder doesn't record that action or the deletion of individual queries. I'd rather just delete the folder in VBA if that's possible.

Thanks.

[#2 · Accepted solution]

@JerryDNYC You need code like this to delete the queries:

Sub DelQueries()
    Dim q As WorkbookQuery
    For Each q In ActiveWorkbook.Queries
        If MsgBox("Delete query '" & q.Name & "'?", vbYesNo) = vbYes Then
            q.Delete
        End If
    Next
End Sub

[#3]

Worked perfectly. Thank you Jan!

[#4]

@Jan Karel Pieterse Is it possible to delete the queries within the active worksheet alone. 

I tried using the below query, but it fails with error. 

Sub Delete_Queries()
       Dim q As WorkbookQuery
       For Each q In ActiveSheet.Queries

[#5]

@sal_iv That requires a different approach:

Sub DelQueries()
    Dim q As WorkbookQuery
    Dim c As WorkbookConnection
    
    For Each c In ActiveWorkbook.Connections
        If c.Ranges.Count > 0 Then
            If c.Ranges(1).Parent.Name = ActiveSheet.Name Then
                c.Delete
            End If
        End If
    Next
End Sub

[#6]

Thanks for the Quick response!!

For me the above quries doesn't remove the Queries in the worksheet.
In the Data & Connections pane, I can see I only have Queries. (Sorry I'm new to these.)

So I edited your code to the following.

```
Sub DelQueries()
    Dim q As WorkbookQuery
    Dim c As WorkbookConnection

    For Each q In ActiveWorkbook.Queries
        If q.Ranges.Count > 0 Then
            If q.Ranges(1).Parent.Name = ActiveSheet.Name Then
                q.Delete
            End If

        Next
End Sub
```

However, it errors out “Object doesn't support this property” for the line "If q.Ranges.Count > 0 Then"

[#7]

Can you please post a screen-shot of your queries and connections pane?

The code sample I gave deletes all Queries which push their result to the active worksheet. I tested it and it works.

[#8]

@Jan Karel Pieterse Much thanks for helping. PFB the screenshot.

As you can see, some are Connection-only and some are with tables. But I do not understand why the Connection-only do not appear in the Connections tab in the above screenshot.

It only appears in the Queries section. 

Also I see that in your code you mention c.Delete. Isn't that deleting only connections. Shouldn't we doing q.Delete ? 

[#9]

You are probably right. Haven't got more time for today though.
I expect you may have to delete both a connection and the query, but I'm not sure.
Perhaps enumerate the listobjects on the sheet and look at their associated query?

[#10]

That is a VBA script, not a Query.