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
Hi @Bubna, Shreya . No idea what happened with this thread. While I was preparing the above answer for you it disappeared from the list of threads on this site. Only way for me to find it out now is by using this link. Anyway, just wanted to ensure you received the above proposal
Apologies i have not been able to test this for my work yet. Thank you so much for helping, i will test it soon and post here if i could work with it or not.
Sign in to comment
VBA to Unlink Excel Tables
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"
.Queries(nameOfQueryToDelete).Delete .Connections("Query - " & nameOfQueryToDelete).Delete
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.
@Lz._ - Please help if possible.
Sign in to comment