Delete a query from excel workbook with Vba

Question

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.

 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

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"?

bilbo_strikes_back · Accepted Answer · 2017-06-29 14:35:46Z

7

Try adding the following line.
The Activeworkbook.Queries() takes a Name or Index

ActiveWorkbook.Queries("Query1").Delete

answered Jun 29, 2017 at 14:35

bilbo_strikes_back

5913 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Salih Over a year ago

But what if we need to delete only from the current sheet? Trying to do the same on ActiveSheet doesn't seem to work.

Craig Cover · Accepted Answer · 2021-09-17 18:57:24Z

2

try this:

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

answered Sep 17, 2021 at 18:57

Craig Cover

211 bronze badge

1 Comment

Salih Over a year ago

How can we remove the queries on the active WorkSHEET alone ?

BruceLee44 · Accepted Answer · 2022-11-28 03:06:43Z

1

Delete all Queries in an Excel Workbook using

Dim Qus As WorkbookQuery

For Each Qus In ActiveWorkbook.Queries
  Qus.Delete
Next

edited Nov 28, 2022 at 3:06

answered Nov 24, 2022 at 14:07

BruceLee44

113 bronze badges

Collectives™ on Stack Overflow

Delete a query from excel workbook with Vba

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Related