How to deny parameter prompt for QueryTables URL?-CodePudding

The following code downloads a .csv file and imports it into a sheet via QueryTables. Unfortunately, the URL contains "[]" which lets Excel think that a parameter is missing (due to URL-Encoding) and forces me to enter an input. I already tried to put something inbetween the percent code but then the URL does not work. Is there a way to avoid the prompt?

Option Explicit
    Dim URL_to_Pull As String
    Dim ws As Worksheet

Sub Pull_Quality()

    Application.Calculation = xlManual
   
    URL_to_Pull = "https://this-is-a-link.com/download/csv?par[]=Hello&report_range=" & Sheets("base").Range("F_Year") & "-" & Sheets("base").Range("F_Month") & "-" & Sheets("base").Range("F_Day") & " - " & Sheets("base").Range("T_Year") & "-" & Sheets("base").Range("T_Month") & "-" & Sheets("base").Range("T_Day")
    Set ws = Sheets("raw_data")
    ws.Range("A:Z").ClearContents
    Debug.Print URL_to_Pull
    Call Connection_Create(URL_to_Pull, ws)
    
    Application.Calculation = xlAutomatic
    
End Sub

Sub Connection_Create(Pull_URL As String, Input_Sheet As Worksheet)

With Input_Sheet.QueryTables.Add(Connection:="URL;" & Pull_URL, Destination:=Input_Sheet.Cells(1, 1))
        .Name = "Query_Table"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Input_Sheet.Cells(1, 1).QueryTable.Delete

Input_Sheet.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False

End Sub

parameter prompt window

CodePudding user response：

https://www.microsoft.com/en-us/microsoft-365/blog/2009/07/31/using-parameters-with-web-queries/ shows how to deal with parameters in web queries. Presumably you can set the value to an empty string if you don't need to provide a value.

Sub Demo() 
    Dim oSh As Worksheet 
    Set oSh = ActiveSheet 
    With oSh.QueryTables.Add( _
       "URL;http://www.jkp-ads.com/Articles/[""PageName""].htm", oSh.Range("A1")) 
        .BackgroundQuery = False 
        MsgBox .Parameters.Count 
        With .Parameters(1)                       '<<<<<<<<<<<<<<<<<<<<
            .SetParam xlRange, oSh.Range("H11") 
            .RefreshOnChange = True 
        End With 
    End With 
End Sub