Forum Discussion
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 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.
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
9 Replies
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 SubJKPieterse 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.Queriessal_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
- Worked perfectly. Thank you Jan!
