-
Lz._ 8,816 Reputation points
2021-03-08T16:19:06.297+00:00 The number of Queries & Connections in a workbook isn't necessarily the same - example with one of my workbooks:
Sub QueriesAndConnectionsCount() Dim WB As Workbook Set WB = ThisWorkbook With WB Debug.Print .Queries.Count 'Result: 5 Debug.Print .Connections.Count 'Result: 3 End With End Sub
So looping with a For i = x to something.Count isn't a good option IMHO. Where possible work with collections and iterate with a For Each
The following should do what you want (in a Module):
Option Explicit Sub DeleteAllQueriesAndConnections() Dim Wbook As Workbook Set Wbook = ThisWorkbook Call DeleteAllQueries(Wbook) Call DeleteAllConnections(Wbook) End Sub Sub DeleteAllQueries(WB As Workbook) Dim WbQuery As WorkbookQuery With WB For Each WbQuery In .Queries WbQuery.Delete Next End With End Sub Sub DeleteAllConnections(WB As Workbook) Dim WbCon As WorkbookConnection With WB For Each WbCon In .Connections .Connections(WbCon.Name).Delete Next End With End Sub
VBA to Unlink Excel Tables

Bubna, Shreya
61
Reputation points
Hello, I have several Power Query loaded tables in my Excel Workbook. I hope to write VBA to delete all Queries but retain the tables. The code is something like
Public Sub DeleteQueryAndConnection()
Dim nameOfQueryToDelete As String
nameOfQueryToDelete = "someQuery"
With ThisWorkbook
.Queries(nameOfQueryToDelete).Delete .Connections("Query - " & nameOfQueryToDelete).Delete
End With
End Sub
But i am trying to modify this to get it to delete all the queries instead of a specific one. Kindly help if posssible. Thank you.
Accepted answer