VBA Macro, get URL from given range loop

Question

The way my code is currently set up, it gets the data from the URL that i've indicated in the code. However, I actually want to provide a list of URLs in Sheet2 that it would loop through until it's extracted all data. I dont want to have to update the code each time individually per URL. There are thousands... How would i be able to do that?

Here is the code:

 Public Sub exceljson()
    Dim https As Object, Json As Object, i As Integer

    Dim Item As Variant

    Set https = CreateObject("MSXML2.XMLHTTP")
    https.Open "GET", "https://min-api.cryptocompare.com/data/price?fsym=USD&tsyms=BTC", False
    https.Send

    Set Json = JsonConverter.ParseJson(https.responseText)

    i = 2
    For Each Item In Json.Items
        Sheets(1).Cells(i, 2).Value = Item
        i = i + 1
    Next
    MsgBox ("complete")
End Sub

Read the Sheet2 range of URLs in a string array and loop the array doing the rest of your code — QHarr, Dec 11 '17 at 15:00
Do you know what that would look like? Im a complete noob :( — Yousuf, Dec 11 '17 at 15:10

Marcucciboy2 · Answer 1 · 2017-12-13T21:36:52.040

2

I'll just pretend that all of the URLS are in Column A here:

Public Sub exceljson()
    Dim https As Object, Json As Object, i As Integer, j As Integer

    Dim Item As Variant

    Set https = CreateObject("MSXML2.XMLHTTP")

    For j = 1 to Sheets(2).UsedRange.Rows.count
        If Len(Trim$(Sheets(2).Cells(j, 1).Value2)) > 0 Then
            https.Open "GET", Trim$(Sheets(2).Cells(j, 1).Value2), False
            https.Send

            Set Json = JsonConverter.ParseJson(https.responseText)

            i = 2
            For Each Item In Json.Items
                Sheets(1).Cells(i, 2).Value = Item
                i = i + 1
            Next Item
        End If
    Next j

    MsgBox ("complete")
End Sub

I like to use the trim() method to be safe that I'm not catching anything extra

edited Dec 13 '17 at 21:36

answered Dec 11 '17 at 15:48

Marcucciboy2

3,156
3
20
38

Hey, i just tried it and it errors out on this line: If Len(Trim$(Sheets(2).Range("A" & i).Value2)) > 0 Then – Yousuf Dec 11 '17 at 17:00
oof, yeah my bad that should be `If Len(Trim$(Sheets(2).Range("A" & j).Value2)) > 0 Then` the key part being that I had put i instead of j initially – Marcucciboy2 Dec 11 '17 at 21:19
1

with thousands of urls i would still consider using an array or collection to loop rather than continually revisiting the worksheet. – QHarr Dec 11 '17 at 21:22
@QHarr that's a really good point, I don't usually work with such large sets of data – Marcucciboy2 Dec 11 '17 at 21:24
Thanks but its still giving me the same error? Is there something else there? Would you be able to try it from your end to see what you get? – Yousuf Dec 12 '17 at 01:37
I didn't follow my initial qualifier when I said "I'll pretend the URLS are in Column A:" `Range("A"&j)` Instead it would be `Range(j&"A")` in both locations – Marcucciboy2 Dec 12 '17 at 05:08
Okay, this time I think I've got it. In a previous edit I had it as `Range("A"&j)` but that wasn't iterating the way that I initially had said it would. Then I had added 1 to count because I was thinking that it was a necessary step since I had shifted from starting index of 0, but the .count actually returns the last row. – Marcucciboy2 Dec 13 '17 at 21:39
That looks a lot better - most people will have the `UsedRange` starting from cell A1, so the possible error introduced by basing row count on `UsedRange` rather than the actual sheet probably won't matter. – YowE3K Dec 13 '17 at 21:39
Thank you very much for helping me through that. I obviously should've made some mock data to debug that and probably would've ironed that out pretty quickly. >_> – Marcucciboy2 Dec 13 '17 at 21:41
1

Yeah, sometimes things look so simple that we don't bother to test, and it's always those times that we trip over the little things :D – YowE3K Dec 13 '17 at 21:42

VBA Macro, get URL from given range loop

1 Answers1