vba excel连接查询表删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17210514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba excel connections query tables delete
提问by luciano
in a bigger Excel application, (25 sheets, 50 command-buttons, 5000 lines of code, 18 userforms ... size 12 MB) ... we load tables from a server as txt files and reconvert these txt_files to excel data with the standard procedure. By macro we delete all querytables and connections after the downloads.
在一个更大的 Excel 应用程序中，（25 个工作表、50 个命令按钮、5000 行代码、18 个用户表单……大小 12 MB）……我们从服务器加载表格作为 txt 文件并将这些 txt_files 重新转换为 excel 数据标准程序。通过宏，我们在下载后删除所有查询表和连接。
Everything worked perfect and quick, but the main sheet is getting slower and slower now. Even opening the Workbook takes more and more time, while the size remains constant.
一切都完美而快速，但主表现在变得越来越慢。即使打开工作簿也需要越来越多的时间，而大小保持不变。
Somehow I have the feeling, there are remainings of the data transfer from the txt_files which are not deleted with:
不知何故，我有一种感觉，从 txt_files 传输的数据的剩余部分没有被删除：
#Dim ws As Worksheet
Dim qt As QueryTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Delete
Next qt
Next ws
If ActiveWorkbook.Connections.Count > 0 Then
For i = 1 To ActiveWorkbook.Connections.Count
ActiveWorkbook.Connections.Item(1).Delete
Next i
EndIf
There are no formulas in the workbook. The slowing down persists, even selecting all cells of the main_sheet + clear ... I suppose all cells are empty after that command...
工作簿中没有公式。减速仍然存在，甚至选择 main_sheet + clear 的所有单元格......我想在该命令之后所有单元格都是空的......
In the other 24 Sheets there are no speed_problems (even filtering a List 850.000 x 33)
在其他 24 页中没有 speed_problems（甚至过滤列表 850.000 x 33）
Any idea how to check what is slowing down the Workbook in the main_sheet? Are there more powerful 'cleanup'commands then deleting querytables and connections? Are there any methods to log what is excel doing, while it does not return the command?
知道如何检查是什么导致 main_sheet 中的工作簿变慢了吗？是否有比删除查询表和连接更强大的“清理”命令？是否有任何方法可以记录 excel 正在做什么，而它不返回命令？
回答by Przemyslaw Remin
Try this:
尝试这个：
ActiveSheet.UsedRange.EntireRow.Delete
Check also in Formula / Named Ranges as with every ListObject table new Named Range is added.
还要检查公式/命名范围，因为每个 ListObject 表都会添加新的命名范围。
Sub DeleteAllNamedRanges()
Dim NR As Name
For Each NR In Application.ActiveWorkbook.Names
NR.Delete
Next
End Sub
回答by Fandango68
This may not be related at all, apologies, but I found the problem for me was not the number of connections, but the number of parameters in each connection.
这可能根本不相关，抱歉，但我发现对我来说问题不是连接数，而是每个连接中的参数数。
I found the "culprit" were obsolete/excessive Parameter objects left behind in the Query Tables themselves. Some of my Queries had around 15 parameters, when the SQL only needed one!
我发现“罪魁祸首”是查询表本身遗留的过时/过多的参数对象。我的一些查询有大约 15 个参数，而 SQL 只需要一个！
Excel does not have an interface that I know of, to deleteParameters once they've been created against a QueryTable. This can happen when you're manually adjusting the SQL code directly in Excel, and saving it with new or extra parameters using the '?' character, per parameter. When you finally settle down on one parameter, all the other parameters are still left behind. I could not find a way to clear/delete them.
Excel没有一个界面，我知道的，要删除参数，一旦他们已经针对创建QueryTable。当您直接在 Excel 中手动调整 SQL 代码并使用“?”将其与新参数或额外参数一起保存时，可能会发生这种情况。字符，每个参数。当您最终确定一个参数时，所有其他参数仍然被抛在后面。我找不到清除/删除它们的方法。
If you ...
如果你 ...
go to Excel -> File Open your workbook -> Stop all macros (if any) -> Click Data -> Queries & Connections to open up all your Queries, etc -> Double-click a Query or Connection and check the Definitions tab and click Parameters..., you may see a huge list of parameters that do the same thing as the first parameter.
转到 Excel -> 文件打开您的工作簿 -> 停止所有宏（如果有）-> 单击数据 -> 查询和连接以打开所有查询等 -> 双击查询或连接并检查定义选项卡和单击参数...，您可能会看到一个巨大的参数列表，它们与第一个参数执行相同的操作。
This can slow Excel exponentially, because for every Query call by Excel to refresh the worksheet/table, it is running that query again, and again 'n' times.
这可能会以指数方式减慢 Excel，因为对于 Excel 每次调用 Query 以刷新工作表/表，它都会再次运行该查询，并再次运行“n”次。
Since Excel does not have a proper interface to manage Parameters, I've had to delete them all using this code and create only ONE parameter per Query.
由于 Excel 没有适当的界面来管理参数，我不得不使用此代码将它们全部删除，并且每个查询仅创建一个参数。
For Each Cn In ActiveSheet.QueryTables
Cn.Parameters.Delete
Next Cn
I could not find a way to delete only one or a few parameters, such as Cn.Parameter(ndx).Delete. There is no such method.
我找不到只删除一个或几个参数的方法，例如Cn.Parameter(ndx).Delete. 没有这样的方法。
