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

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?