使用 Vba 从 Excel 工作簿中删除查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44827417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete a query from excel workbook with Vba
提问by dgard
I am pretty new to VBA and am learning how to add connections to databases in excel. I currently have a macro that creates a query called "Query1" it then queries my database and returns the correct table. I would like to be able to delete the query after the table is output to the excel sheet so that I can run the macro again with slightly modified conditions eg different dates.
我对 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")).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
I would like to add something like the following after the table is placed into the worksheet
将表格放入工作表后，我想添加如下内容
ActiveWorkbook.Queries.Delete = Name: = "Query1"
But obviously this doesn't exist. How can I remove the query or make it so that the macro can be run without having to delete "Query1"?
但显然这并不存在。如何删除查询或使其能够运行宏而不必删除“Query1”？
回答by bilbo_strikes_back
Try adding the following line.
The Activeworkbook.Queries() takes a Name or Index
尝试添加以下行。
Activeworkbook.Queries() 采用名称或索引
ActiveWorkbook.Queries("Query1").Delete
