VBA to Unlink Excel Tables

Question

question

BubnaShreya-0514 asked · Mar 08 at 12:16 PM

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

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

BubnaShreya-0514 · Mar 08 at 12:22 PM

@Lz-3068 - Please help if possible.

Thank you.

0 ·

Answer 1 · 2021-03-08T16:19:06.297Z

Lz-3068 answered · Mar 08 at 04:19 PM

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

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

Lz-3068 · Mar 12 at 08:21 AM

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 ·

BubnaShreya-0514 Lz-3068 · Mar 12 at 09:40 AM

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 ·

question