VBA Excel 连接查询表删除

Question

在一个更大的Excel应用程序中（25个工作表，50个命令按钮，5000行代码，18个用户窗体...大小为12 MB）...我们使用标准程序从服务器加载txt文件并将这些txt文件重新转换为Excel数据。通过宏，我们在下载后删除所有查询表和连接。

一切都运行得很完美和快速，但是主工作表现在变得越来越慢。 即使大小保持不变，打开工作簿所需的时间也越来越长。

我有一种感觉，从txt文件的数据传输中仍然存在未被删除的残留物:

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

工作簿中没有公式。即使选择主要工作表的所有单元格并清除...我认为在该命令之后所有单元格都为空，但减速仍然存在...

其他 24 个工作表中没有速度问题（即使筛选一个 850,000 x 33 的列表）

有什么想法可以查看是什么在拖慢主要工作表的工作簿？ 是否有更强大的“清理”命令来删除查询表和连接？ 是否有任何方法记录 Excel 在执行命令时正在做什么，而不返回命令？

- luciano

---

我在某处读到，从Excel 2007开始，您必须在每个ActiveSheet.ListObjects中处理QueryTable。 - Antony

如果您想清除主表格，您应该选择全部内容并在键盘上按下（Control & -），即按住 Control 键并同时按下数字键盘上的减号键，这将删除所有单元格，而不仅仅是清除内容，从而释放一些内存... - Xabier

Answer 1

这可能完全不相关，抱歉，但对我来说问题不在于连接数，而在于每个连接中的参数数量。我发现“罪魁祸首”是在查询表本身中留下的过时/过多的参数对象。我的一些查询有大约15个参数，而SQL只需要一个！Excel没有我所知道的接口，可以删除一旦针对QueryTable创建了参数就无法删除它们。当您直接在Excel中手动调整SQL代码并使用“？”字符为每个参数保存新或额外的参数时，就会发生这种情况。当您最终在一个参数上定下来时，所有其他参数仍然留在那里。我找不到清除/删除它们的方法。如果您...去Excel->文件打开您的工作簿->停止所有宏（如果有）->单击“数据”->查询和连接以打开所有查询等->双击查询或连接并检查“定义”选项卡，然后单击“参数...”，您可能会看到一个巨大的参数列表，其与第一个参数执行相同的操作。

这可能会使Excel变得非常缓慢，因为每次Excel调用查询以刷新工作表/表格时，它都会再次运行该查询，并且会重复'n'次。

由于Excel没有适当的界面来管理参数，我必须使用此代码将它们全部删除，并仅为每个查询创建一个参数。

For Each Cn In ActiveSheet.QueryTables
        Cn.Parameters.Delete
Next Cn

我无法找到一种只删除一个或几个参数的方法，比如Cn.Parameter(ndx).Delete。 没有这样的方法。

- Fandango68

Answer 2

试试这个：

ActiveSheet.UsedRange.EntireRow.Delete

同时在公式/命名区域中检查，因为每个ListObject表都会添加新的命名区域。

Sub DeleteAllNamedRanges()
Dim NR As Name
For Each NR In Application.ActiveWorkbook.Names
    NR.Delete
Next
End Sub

- Przemyslaw Remin