我是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"?
尝试添加以下行。
ActiveWorkbook.queries((取名或索引
ActiveWorkbook.Queries("Query1").Delete
尝试以下:
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
使用
删除Excel工作簿中的所有查询Dim Qus As WorkbookQuery
For Each Qus In ActiveWorkbook.Queries
Qus.Delete
Next