VBA- wait for response request/web API

Question

I am trying to get the order book from bitmex to excel. On the basis of examples on the Internet, I managed to write only part of the code. It works only once and I still need to make loops so that it works automatically. So that the next request will be made after downloading the data.

I used the json converter library downloaded from github and added in references:

• microsoft xml6.0 and microsoft winhttp verion 5.1

---

Public Sub bitmexAPI()
    Dim http As Object, JSON As Object, i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", "https://www.bitmex.com/api/v1/orderBook/L2?symbol=XBT&depth=5", False
    http.Send

    Set JSON = ParseJson(http.ResponseText)
    i = 2

    For Each Item In JSON
        Sheets(2).Cells(i, 1).Value = Item("symbol")
        Sheets(2).Cells(i, 2).Value = Item("id")        
        Sheets(2).Cells(i, 3).Value = Item("side")        
        Sheets(2).Cells(i, 4).Value = Item("size")        
        Sheets(2).Cells(i, 5).Value = Item("price")                
        i = i + 1        
    Next

    Application.OnTime Now + TimeValue("00:00:02"), "bitmexAPI"                   
End Sub

I added a delay of 2 seconds but only once refreshes the data. How to make loops so that the macro will wait for the response from the previous query?

Answer 1

Try this:

While http.readyState <> 4
DoEvents
Wend