How to unlink a query from a structured table AND deleting that query in VBA

Question

I know that since Excel 2016 I can simply delete a query like this:

ActiveWorkbook.Queries("aaa").delete

However this will leave an "orphaned" query connection. So if I refresh the remaining structured table, I will see the error

 Query xxx was not found

I know that I can avoid this by first unlinking the query like this:

ActiveSheet.ListObjects("aaa").Unlink

How can I combine these 2 codes into a macro that:

Takes the query name as input (name in the Power Query Editor list)
Remove the link to the structured table
Delete that query

I prefer a method that works in the Queries collection of ActiveWorkbook. Because ListObjects is Sheet-dependent, and sheet name may change. Something like this:

ActiveWorkbook.Queries("aaa").[something].unlink
ActiveWorkbook.Queries("aaa").delete

chillin · Accepted Answer · 2019-06-09 10:37:25Z

Regarding ListObject.Unlink method, documentation (https://learn.microsoft.com/en-us/office/vba/api/excel.listobject.unlink) suggests:

Removes the link to a Microsoft SharePoint Foundation site from a list. Returns Nothing.

Doesn't seem relevant, as you don't mention SharePoint. Perhaps the code below is what you want.

Option Explicit

Private Sub DeleteQueryAndConnection()

    Dim nameOfQueryToDelete As String
    nameOfQueryToDelete = "someQuery"

    With ThisWorkbook
        .Queries(nameOfQueryToDelete).Delete
        .Connections("Query - " & nameOfQueryToDelete).Delete
    End With

End Sub

I didn't get the "Query xxx was not found" error after running this code.

Collectives™ on Stack Overflow

How to unlink a query from a structured table AND deleting that query in VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related