使用VBA从Excel工作簿中删除查询

Question

我对VBA还比较陌生，正在学习如何在Excel中添加数据库连接。我目前有一个宏，可以创建一个名为"Query1"的查询，然后查询我的数据库并返回正确的表格。我希望能够在将表格输出到Excel表格后删除查询，以便我可以再次运行宏并使用略微修改的条件，例如不同的日期。

 Sub Macro2()
'
' Macro2 Macro
'


ActiveWorkbook.Queries.Add Name:="Query1", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Odbc.Query(""dsn=Database"", ""SELECT DISTINCT c.IP_TREND_VALUE AS """"PRODUCT"""", c.IP_TREND_TIME , s.IP_TREND_TIME AS TIMES, s.IP_TREND_VALUE AS """"Wttotal""""#(lf)FROM """"Product"""" AS c , """"wtTotal"""" as s#(lf)#(lf)Where  #(lf)#(tab) c.TIME Between '1-JUN-17 05:59:00' AND '2-" & _
    "JUN-17 05:59:00' AND c.TIME = s.IME#(lf)"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source" & _
    ""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [Query1]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Query1"
    .Refresh BackgroundQuery:=False
End With

End Sub

在将表格放入工作表后，我想添加类似以下内容的东西。

ActiveWorkbook.Queries.Delete = Name: = "Query1"

但很明显这是不存在的。我该如何删除查询或使宏能够在不必删除“Query1”的情况下运行？

- dgard

Answer 1

尝试添加以下行。
Activeworkbook.Queries()需要名称或索引。

ActiveWorkbook.Queries("Query1").Delete

- bilbo_strikes_back

---

但是如果我们只需要从当前工作表中删除呢？尝试在ActiveSheet上执行相同的操作似乎不起作用。 - Salih

Answer 2

试试这个：

For Each Worksheet In ThisWorkbook.Worksheets
    Qcount = Worksheet.QueryTables.Count
    If Qcount > 0 Then
        For Each QueryTable In ws.QueryTables
            QueryTable.Delete
        Next
    End If
Next Worksheet

- Craig Cover

---

我们如何仅移除活动工作表上的查询？ - Salih

Answer 3

使用以下方法删除Excel工作簿中的所有查询

Dim Qus As WorkbookQuery

For Each Qus In ActiveWorkbook.Queries
  Qus.Delete
Next

- BruceLee44