Forum Discussion

Copper Contributor

Oct 16, 2022

Solved

VBA to delete Power Query Connections or Folder

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 wan...

excel

Macros and VBA

JKPieterse

Oct 17, 2022

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

sal_iv

Copper Contributor

Feb 08, 2023

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"

JKPieterse

Silver Contributor

Feb 08, 2023

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.

sal_iv
Copper Contributor
Feb 08, 2023
JKPieterse 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 ?
- JKPieterse
  Silver Contributor
  Feb 08, 2023
  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?