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.

{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points

    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  
        End With  
    End Sub  
    Sub DeleteAllConnections(WB As Workbook)  
        Dim WbCon   As WorkbookConnection  
        With WB  
            For Each WbCon In .Connections  
        End With  
    End Sub  

0 additional answers

Sort by: Most helpful