vba excel连接查询表删除

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(甚至过滤List 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正在做什么,而它不返回命令?

1 个解决方案

#1

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