GET 请求数据不更新 [英] GET request data doesn't update

问题描述

我正在尝试从 API 检索数据，但即使我在 GET 请求之前将其设置为空，我的变量也不会更新.

I'm trying to retrieve data from an API but my variable doesn't update even if I set it as nothing before the GET request.

变量的数据只有在我关闭Excel并重新打开它时才会更新.

The data of the variable update only if I close Excel and re-open it.

有什么解释吗?我一直在挠头这么久.

Is there any explanation for it? I've been scratching my head for so long.

这是代码

Sub getJsonResult()
    Dim objRequestt As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strUrlXBTUSD As String
    Dim strResponse As String
    Dim jsonText As String
    Dim jsonObject As Object, item As Object
    Dim i As Integer

    'setting up the variable to 0 or nothing

    strUrlXBTUSD = ""
    strResponsee = ""
    jsonText = ""
    i = 0
 blnAsync = False
 Set item = Nothing
 Set jsonObject = Nothing
 Set objRequestt = Nothing
 Set objRequestt = CreateObject("MSXML2.XMLHTTP")
    strUrlXBTUSD = "https://www.bitmex.com/api/v1/orderBook/L2?symbol=XBTUSD&depth=3"
    blnAsync = True


    'Starting the GET request

    ThisWorkbook.Activate
    With objRequestt
        .Open "GET", strUrlXBTUSD, blnAsync
        .SetRequestHeader "Content-Type", "application/json"
        .send
        strResponse = .responseText 'here the response is always the same except if i Close Excel
           Debug.Print strResponsee
    End With
End Sub

即使在几次 F5 刷新后，strResponse"也始终相同.我可以看到数据在 Web 浏览器上不再准确.我希望 VBA 程序能够在不关闭 Excel 的情况下获取准确的数据并刷新.

At the end "strResponse" is always the same even after several F5 refresh. I can see that the data are no longer accurate on a web browser. I'd like the VBA program to get accurate data and refresh without closing Excel.

怎么做?

推荐答案

您可以添加一条指令以避免被提供缓存结果(服务器可以忽略这一点，但我过去在这方面取得了很好的成功).确保您的异步参数始终为 False，并在测试之间留出更多时间.我注意到有时价格变化缓慢，因此您可能会因为间隔太小/尝试次数不足而错过更改.不过，您会注意到 size 发生了变化.您应该向在底部脚本中循环.

You can add an instruction to avoid being served cached results (server can ignore this but I have had good success with this in the past). Make sure your async argument is always False and leave more time between tests. I notice that sometimes the prices are slow to change so you may miss the change due to too small an interval/not enough attempts. You will notice the size change though. You should add a max timeout to the loop in the bottom script.

还删除了匈牙利符号.

Option Explicit

Public Sub getJsonResult()
    Dim http As Object
    Dim urlXBTUSD As String
    Dim response As String
    Dim j As Long
    Const ASYNC_ARG As Boolean = False

    Set http  = CreateObject("MSXML2.XMLHTTP")
    For j = 1 To 10
        response = vbNullString
        urlXBTUSD = "https://www.bitmex.com/api/v1/orderBook/L2?symbol=XBTUSD&depth=3"

        With http 
            .Open "GET", urlXBTUSD,  ASYNC_ARG
            .setRequestHeader "Content-Type", "application/json"
            .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
            .send
            response = .responseText
            Debug.Print response
        End With
        Application.Wait Now + TimeSerial(0, 0, 15)
    Next
End Sub

<小时>

这是一个漫长而乏味的证明方法，它通过循环直到返回集合中第一个项目的价格发生变化.我使用 jsonconverter.bas 添加到项目和 VBE> 工具 > 参考 > Microsoft 脚本运行时参考.

---

Here is a long and tedious way of proving it by looping until price of first item in return collection changes. I use jsonconverter.bas added to project and VBE > Tools > References > Microsoft Scripting Runtime reference.

Option Explicit

Public Sub getJsonResult()
    Dim http  As Object
    Dim urlXBTUSD As String
    Dim response As String
    Dim j As Long
    Const ASYNC_ARG As Boolean = False
    Dim price As String, firstValue As String

    Set http  = CreateObject("MSXML2.XMLHTTP")
    urlXBTUSD = "https://www.bitmex.com/api/v1/orderBook/L2?symbol=XBTUSD&depth=3"

    With http 
        .Open "GET", urlXBTUSD,  ASYNC_ARG
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
         firstValue = JsonConverter.ParseJson(.responseText)(1)("price")
        Debug.Print  firstValue
        Do
            .Open "GET", urlXBTUSD, blnAsync
            .setRequestHeader "Content-Type", "application/json"
            .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
            .send
            price = JsonConverter.ParseJson(.responseText)(1)("price")
            Application.Wait Now + TimeSerial(0, 0, 5)
        Loop While price = firstValue
        Debug.Print price
    End With
End Sub

这篇关于GET 请求数据不更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！