SOLVED

VBA to delete Power Query Connections or Folder

New Contributor

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 Replies
best response confirmed by Grahmfs13 (Microsoft)
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
Worked perfectly. Thank you Jan!