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"也始终相同.我可以看到数据在网络浏览器上不再准确.我希望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
在这里,通过循环直到退货中第一个商品的价格发生变化,证明它是一种漫长而乏味的方法.我使用添加到项目和VBE中的 jsonconverter.bas >工具>参考> 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屋!