vba - 通过 Excel VBA 完全删除工作表查询

Question

我有创建查询表的代码。创建它后，我取出我想要的数据，然后完成该查询。我不希望一堆查询表占用空间，所以我想立即删除它。我用的是

QueryTables.Delete

ActiveWorkbook.Connections.Item(i).Delete

问题是查询表连接仍然存在。因此，如果我尝试使用相同的名称进行另一个查询，它会告诉我该查询已经存在并且无法执行。

我希望该表现有的任何记录都完全消失。

这是我的代码:

Sub Macro8()

Dim currency1 As String
Dim currency2 As String

currency1 = ActiveSheet.Range("currency1")
currency2 = ActiveSheet.Range("currency2")

Range("clear").Select
Selection.ClearContents

ActiveWorkbook.Queries.Add Name:="FXFWD", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""http://www.investing.com/currencies/" & currency2 & "-" & currency1 & "-forward-rates""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{"""", type text}, {""Name"", type text}, {""Bid"", type number}, {""Ask"", type number}, {""High"", type number}, {""Low"", type number}, {""Chg."", type number}, {""Time""," & _
    " type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Book1"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=FXFWD" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [FXFWD]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = False
    .ListObject.DisplayName = "FXFWD"
    .Refresh BackgroundQuery:=False
End With

Sheets("Book1").Rows("1:33").Copy
Sheet1.Rows("18").PasteSpecial xlPasteValues
Sheets("Book1").Select

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim con As Object
Dim ws As Worksheet
Dim qryT As QueryTable

For Each con In ThisWorkbook.Connections
    con.Delete
Next con
For Each ws In ThisWorkbook.Worksheets
    For Each qryT In ws.QueryTables
         qryT.Delete
    Next qryT
Next ws

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheet1.Select
End Sub

Answer 1

For Each qr In ThisWorkbook.Queries
    qr.Delete
Next qr