Crashing with loop of xmlhttp requests

Question

I am trying to rewrite this macro from ie.application to http requests in VBA. The macro navigates to a URL, GETs the html then parses and scrapes the required data.

It works well with one request, but when I try to do this with a FOR loop with thousands of URLs Excel crashes.

I think I should "close" the connection in some way before going to the next loop, but I did not find a solution.

Here is my code:

Sub GetQuotes()
    Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
    Dim html As New HTMLDocument


    For r = 1 To 10

'*****GO TO PRODUCT PAGE*****
        path= ThisWorkbook.Worksheets("Sheet1").Cells(r, 1).Value
        myurl = "https://some_domain.com" + path

        xmlhttp.Open "GET", myurl, False
        xmlhttp.send
        html.body.innerHTML = xmlhttp.responseText



'*****GET PRICE*****
        If Not html.getElementById("some_id") Is Nothing Then
            price = html.getElementById("some_id").innerHTML
            ThisWorkbook.Worksheets("sheet1").Cells(r, 2).Value = price
        Else
            price = "empty"
            ThisWorkbook.Worksheets("sheet1").Cells(r, 2).Value = price
        End If

        Next r

    End Sub

XML and HTML references are active

Answer 1

Squeeze in a

Do While xmlhttp.ReadyState <> 4
    DoEvents
Loop

after the send. Your current code will fire all requests almost at once. With this, you will wait for the page to load before proceeding.