VBA to Unlink Excel Tables

Bubna, Shreya 61 Reputation points
2021-03-08T12:16:40.01+00:00

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.

Office Visual Basic for Applications
Office Visual Basic for Applications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Visual Basic for Applications: An implementation of Visual Basic that is built into Microsoft products.
1,502 questions
{count} votes

Accepted answer
  1. Lz._ 9,011 Reputation points
    2021-03-08T16:19:06.297+00:00

    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  
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.