CODEWITHSUNDEEP
excelwebparameters
user1431340
user1431340

Reputation: 55

Parameters in a Excel Web Query

I'm trying to import data from the web and it works fine for a static URL. The URL looks something like this: http://www.foo.com/api/v0.9/csv/items/3/1 The last number (here "1") controls the page count. As there is about 200 pages in total I'd rather not remake 200 static queries, so my question is how can I parametrize this?

I've searched the internet and the only solution I found it changing the URL in something like http://www.foo.com/api/v0.9/json/items/3.html?page=1 But my URL just works differently so the solutions don't apply.

Can anyone tell me how it can be parametrized in my case or point me in the right direction?

Thank you for your time

Upvotes: 1

Views: 814

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

You can change QueryTable.Connection property to point to a different URL. Here's an example

Dim i As Long
Dim qt As QueryTable

For i = 1 To 200
    Set qt = Sheet1.QueryTables(1)
    qt.Connection = "URL;http://www.foo.com/api/v0.9/csv/" & i
    qt.Refresh False


    'Do some stuff with the data
Next i

Upvotes: 1

Related Questions