QueryTables.Add dynamic link referencing cells-CodePudding

The link I want to use is "https://xxx/xxx/xxx/reporting?start_date=2022-05-15 00&end_date=2022-05-21 00" whereas the start_date=Simba1 and the end_date=Simba2

Simba 1 and simba 2 are date cells.

'Download Simba Table'

Sheets("SIMBA Data").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://xxx/xxx/xxx/reporting?" _
    & "start_date=" & Simba1 _
    & "&end_date=" & Simba2 & "", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

CodePudding user response：

Use Format()

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://xxx/xxx/xxx/reporting?" _
    & "start_date=" & Format(Simba1, "yyyy-mm-dd") _
    & " 00&end_date=" & Format(Simba2, "yyyy-mm-dd") & " 00", _
    Destination:=Range("$A$1"))
   
    '...
    '...

CodePudding user response：

Adding the following with the answer that Tim posted, resolved my issue!!

Dim Simba1 As Variant

Simba1 = Range("'Report Controls'!$C$1").Value

Dim Simba2 As Variant

Simba2 = Range("'Report Controls'!$C$2").Value