question

BubnaShreya-0514 avatar image
0 Votes"
BubnaShreya-0514 asked ·

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"
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-vba-dev
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Lz-3068 - Please help if possible.

Thank you.

0 Votes 0 ·

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @BubnaShreya-0514

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


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BubnaShreya-0514. 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

1 Vote 1 ·

Hello,

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.

0 Votes 0 ·