Dynamics webquery from url in cells

Question

I searched the internet, but I could not find anywhere a dynamic query from links in cells. In excel i have webquery which generate this data:

I have data in excel Sheet("CustomReport"):

SalesOrder  Value1      Value2      Value3     Links
1           Jonas       Station1    8          https://x.com=1
2           Greg        Station1    5          https://x.com=2
3           Anton       Station1    1          https://x.com=3
...         ...         ...         ...        ...

Number of rows in this query is always different when it is refreshed.

And based on this webquery i need generate dynamic webquery in macro. For example: DynamicQuery1 save data from report https://x.com=1 to Sheet name "Orders" started from A1 and ending A{X} value (reports have diffrents number of rows).

DynamicQuery2 save data from report https://x.com=2 to the same Sheet "Orders" but started from A{X+1}.

I have such a macro, but it only works for the first row.

Sub Test()

Dim URL As String

URL = Sheets("CustomReport").Range("E2").Value

Sheets("Orders").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("$A$1"))
    .Name = "team2289_2"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingAll
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = True
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

And i need refreshing this macro every 1 hour. Anyone can give me macro based on this way?

Answer 1

To loop through the cells use:

dim c as range, DataSpot as range
set c = Sheets("CustomReport").Range("E2")
while c.value <>""
    url = c.value
    set DataSpot=cells(sheets("orders").Range("A1").SpecialCells(xlLastCell).row+1,1)
    ' Web Query Code goes here
    set c=c.offset(1,0)
wend

In your data query, use:

Destination:=Range(DataSpot.Address))