|
On Mar 22, 2017, at 7:27 PM, 方家晟 wrote:
Micheal您好，
謝謝分享。之前提到的｢每更新10支自動存檔｣，建議在存檔這個動作時順便清理一次QueryTable減輕運行的負擔。
方案一
放置在ThisWorkbook內觸發：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim qt As QueryTable
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Delete
Next
Next
End Sub
方案二
或是更建議將它變成一個獨立的Function(可減少重複的程式碼)放在模組內，讓不同程序都可以使用：
Function QueryTablesDelete()
Dim ws As Worksheet
Dim qt As QueryTable
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Delete
Next
Next
End Function
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
QueryTablesDelete
End Sub
只要在儲存前先清除(關閉不儲存則無須清除)，那麼就不需要擔心檔案大小變肥、佔資源。
方案三
若在查完一家公司資料之後就清除(則無須放在BeforeSave觸發重覆清除)，進一步帶入變數，將Function改成可指定工作表：
Function QueryTablesDelete(ash As String)
Dim ws As Worksheet
Dim qt As QueryTable
If ash = "all" Then
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Delete
Next
Next
Else
For Each qt In Worksheets(ash).QueryTables
qt.Delete
Next
End If
End Function
需將程式碼改成這一句，指定的工作表名稱放置在QueryTablesDelete後面：
例1.清除使用中的工作表內的QueryTables
QueryTablesDelete ActiveSheet.Name
例2.依需求修改，如清除指定"台股"、"美股"、"港股"...工作表內的QueryTables
QueryTablesDelete "台股"
例3.必須且只有一個變數，若不指定工作表，則需以"all"替代，清除所有工作表內的QueryTables
QueryTablesDelete "all"
|
