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?

Welcome to SO! When referencing code, be sure to properly format your answer by indenting your code/error sections with 4 spaces on each line. — technogeek1995
– technogeek1995, Commented Jun 24, 2019 at 18:10

double-beep · Accepted Answer · 2019-06-24 19:19:25Z

2

Try this:

While http.readyState <> 4
DoEvents
Wend

edited Jun 24, 2019 at 19:19

double-beep

5,67019 gold badges43 silver badges50 bronze badges

answered Jun 24, 2019 at 18:55

yuro

461 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Krzysiek Over a year ago

I wrote this code in different places but the data in the sheet do not change ... only when I open the file only once it changes. Maybe I need to clear the variables since the data stored there is not updated itself?

yuro Over a year ago

Apologies, but can you restate your problem? I thought it was just 'How to make loops so that the macro will wait for the response from the previous query?', did the code not work? what is the error message?

Krzysiek Over a year ago

I don't have the error message, everything works, the macro in my previous code (the first one) is done in the time interval as I wanted (2 seconds). The problem is that the data in the worksheet are not refreshed ... they change once and after that there are no more changes. And the macro is still doing every two seconds, nothing changes.Only the first time, the data in the spreadsheet changes, later no longer.I would like every time the macros are made, the data will change in the sheet.

Collectives™ on Stack Overflow

VBA- wait for response request/web API

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related