a query with the name already exists

Question

so I want to create an automated querying for Yahoo Finance historical data (csv download) using Excel VBA. I set up a function so Excel would automatically query the ticker symbol alongside its start and end dates (K1, K2, K3 respectively).

Here is the code:

Sub YFIN_get()
'
' YFIN_get Macro
'
Dim ticker As String, sday, eday As Long
Columns("A:G").ClearContents
ticker = Range("K1")
sday = Range("K2")
eday = Range("K3")
'
    ActiveWorkbook.Queries.Add Name:="Table 4", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""https://query1.finance.yahoo.com/v7/finance/download/" & ticker & "?period1=" & sday & "&period2=" & eday & "&interval=1d&events=history""),[Delimiter="","", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Use First Row as Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table" & _
        ".TransformColumnTypes(#""Use First Row as Headers"",{{""Date"", type date}, {""Open"", type number}, {""High"", type number}, {""Low"", type number}, {""Close"", type number}, {""Adj Close"", type number}, {""Volume"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Change Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 4"";Extended Properties="""""), Destination:=Range("$A$1")). _
        QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 4]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_4"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

However, VBA always retrieves runtime error "a query with the name 'Table 4' already exists" whenever I run the code for the second time. When I debug the error it highlights the ActiveWorkbook.Queries.Add Name..... part.

Can anybody help me with the solution? Perhaps deleting the query? If so how should I delete the query? (I'm completely new to VBA so your help is much appreciated)

Answer 1

You can use this before trying to add it:

On Error Resume Next
ActiveWorkbook.Queries("Table 4").Delete
On Error GoTo 0