QueryTables.Add dynamic link referencing cells

0
votes

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
2
excelvba
Would help to explain what the problem is - what does it do instead of work? Also include the declarations for Simba1/2 variables and where you assign them a value. Likely you want to use something like Format(Simba1,"yyyy-mm-dd") & "+00" for each date. – Tim Williams
This is webscraping a table, in this case it is table ID #1. The Simba 1/2 variables are simply referencing cells F1 and F2 which are the dates that a user will enter. – Justin
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community

2 Answers

1
votes

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
0
votes

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"))
   
    '...
    '...