Delete all queries in a workbook

Question

I am trying to write a sub to delete all queries in a workbook. I have got the code below:

Dim CN As Variant
Dim qTable As QueryTable



For Each CN In ThisWorkbook.Connections
    CN.Delete
Next CN

 For Each qTable In Sheets("Property Extract 1").QueryTables
qTable.Delete
Next qTable'

The connection deletion works fine but the queries remain. Any ideas how to delete all queries in a workbook?

I was planning to replicate the query deletion for 2 or 3 sheets.

thanks

Posting this for anyone else stumbling onto this thread. I had a ss that over time became unresponsive and it ended up being that all the connections from web scraping were bogging it down. I used the code in this thread to delete all connections and the issue was fixed. Hopefully, this helps others with similar issues. — imnewhere, Sep 11 '21 at 16:42

score 13 · Accepted Answer · answered Mar 08 '18 at 18:23

First off, you cannot delete PQ Queries from VBA unless you are on Excel 2016/365. PQ is not exposed to VBA in earlier versions even when the add-on is installed. If you have the right version of Excel, you can delete the PQ Queries themselves just like you are clearing the connections:

Dim pq As Object
For Each pq In ThisWorkbook.Queries
    pq.Delete
Next

shrivallabha.redij · Answer 2 · 2018-03-07T06:37:29.077

3

Just add another loop to loop through sheets.

Dim cn
Dim qt As QueryTable
Dim ws As Worksheet
For Each cn In ThisWorkbook.Connections
    cn.Delete
Next
For Each ws In ThisWorkbook.Worksheets
    For Each qt In ws.QueryTables
        qt.Delete
    Next
Next ws

Edit: If you are adding using Query Wizard then perhaps you'd need modified code below.

Dim cn
Dim qt As QueryTable
Dim lo As ListObject
Dim ws As Worksheet
For Each cn In ThisWorkbook.Connections
    cn.Delete
Next
For Each ws In ThisWorkbook.Worksheets
    For Each qt In ws.QueryTables
        qt.Delete
    Next qt
    On Error Resume Next 'Ignore error if there's no query in table.
    For Each lo In ws.ListObjects
        lo.QueryTable.Delete
    Next lo
    On Error Goto 0
Next ws

edited Mar 07 '18 at 06:37

answered Mar 06 '18 at 16:05

shrivallabha.redij

5,832
1
12
27

Thanks but the queries still remain in the queries and connection list under the data tab. – Chris Mar 06 '18 at 16:39
@Chris Weird. I don't get such issues. What option are you using to create query in data tab? – shrivallabha.redij Mar 06 '18 at 16:50
@Chris Check the edited post. See if it gets you through. – shrivallabha.redij Mar 06 '18 at 16:58
lo.QueryTable.Delete throws an error, its strange because the variable lo is picking up my query2 – Chris Mar 06 '18 at 17:03
@Chris it will happen if there's table without query. Unfortunately there's no direct way of detecting it so I've used `On Error Resume Next`. See edited code. – shrivallabha.redij Mar 07 '18 at 06:38
Sorry I didn't answer your question. I am using vba to add the query. – Chris Mar 07 '18 at 07:50
Unfortunately the revised code doesnt remove the queries. Is it possible that this is a bug? – Chris Mar 07 '18 at 09:20
@Chris I am not sure. Is it possible to upload file somewhere and post a link if information is not confidential? I will try to have a look. – shrivallabha.redij Mar 07 '18 at 12:52
Delayed response but the solution proved "querytables" didnt work. Queries did – Chris Mar 26 '18 at 19:27

score 2 · Answer 3 · edited Apr 07 '21 at 06:15

2

Clear all connection in activeworkbook without any error. you must convert the Object to as String in order delete all connections

Sub ClearQueries()
    Dim pq As Object
    Dim q As String
    For Each pq In ThisWorkbook.Queries
        q = pq
        ActiveWorkbook.Queries(q).Delete
    Next
End Sub

edited Apr 07 '21 at 06:15

David Buck

3,752
35
31
35

answered Apr 06 '21 at 22:48

Tee

21
1

score 0 · Answer 4 · answered Oct 06 '20 at 09:17

0

I Excel 2016 I was able to select all by maintaining CTRL and clicking on each in the Workbook queries pane. Then right-click --> Delete.

answered Oct 06 '20 at 09:17

Guppy

426
5
9

Delete all queries in a workbook

4 Answers4

Linked