CODEWITHSUNDEEP
excelvbapowerquery
Chris
Chris

Reputation: 192

Delete all queries in a workbook

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

Upvotes: 10

Views: 29584

Answers (4)

Tee
Tee

Reputation: 21

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

Upvotes: 2

Guppy
Guppy

Reputation: 426

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.

Upvotes: 0

Wedge
Wedge

Reputation: 1826

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

Upvotes: 14

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

Upvotes: 3

Related Questions