删除工作簿中的所有查询 [英] 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?

我计划复制 2 或 3 张查询删除.

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

谢谢

推荐答案

首先，除非使用 Excel 2016/365，否则无法从 VBA 中删除 PQ 查询.即使安装了附加组件，PQ 也不会在早期版本中暴露给 VBA.如果您拥有正确版本的 Excel，您可以像清除连接一样删除 PQ 查询本身:

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

这篇关于删除工作簿中的所有查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！