VBA to Unlink Excel Tables

Question

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.

Answer 1

Hi @Bubna, Shreya

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