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?

Greg Viers · Accepted Answer · 2018-01-17T16:17:51.737

0

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))

edited Jan 17 '18 at 16:17

answered Jan 17 '18 at 14:22

Greg Viers

3,473
3
18
36

i wrote @up full macro, can u help me ? – Seva Arve Jan 17 '18 at 15:04
I fixed the offset line. I mistyped it originally. – Greg Viers Jan 17 '18 at 15:19
its not work, because macro download x times only raport from cell "e2" and i need download 1 times every raport from cells e{x} – Seva Arve Jan 17 '18 at 15:27
I've added another line: url=c.value. – Greg Viers Jan 17 '18 at 15:40
url=c.value must be the same line like while or add tab before url = c.value? – Seva Arve Jan 17 '18 at 16:16
It's within the while loop. – Greg Viers Jan 17 '18 at 16:18
still download raport from first link 'x' times from cell "e2", but not download report from link from cell "e3,e4" etc. maybe i dont need while loop, but formula: for each rows in column "e" make webquery ? – Seva Arve Jan 17 '18 at 16:54
i have quesion - how add command - for new quering macro - clean all rows in Sheet1 – Seva Arve Jan 17 '18 at 17:06
If the solution works, perhaps mark my solution as correct and then post a new question for your next issue – Greg Viers Jan 17 '18 at 19:05
i posted new issue :) – Seva Arve Jan 17 '18 at 19:21

score 0 · Answer 2 · answered Jan 17 '18 at 15:03

Sub Test()

Dim URL As String

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

Sheets("Sheet1").Select
Dim c As Range, DataSpot As Range
Set c = Sheets("CustomReport").Range("E2")
While c.Value <> ""
    Set DataSpot = Cells(Sheets("Sorders").Range("A1").SpecialCells(xlLastCell).Row + 1, 1)
        With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range(DataSpot.Address))
         .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
        Set r = r.Offset(1, 0)
Wend

End Sub

i put your code and i have debug with: Set r = r.Offset(1, 0)

What wrong i do ?

Dynamics webquery from url in cells

2 Answers2