Excel VBA调用API重复获取相同数据问题求助

你的问题核心是MSXML2.XMLHTTP60默认会缓存GET请求的响应，导致后续请求直接读取本地缓存，而非向NSE服务器获取最新数据。另外NSE的API会验证请求合法性，缺少必要请求头也可能触发缓存返回。

修复方案： #

• 添加禁用缓存的请求头，强制服务器返回最新数据
• 模拟浏览器请求，添加User-Agent等必要标识，避免被服务器判定为非合法请求

修改后的完整代码： #

Sub N_Test_33()
    Dim req As New MSXML2.XMLHTTP60
    Dim URL As String, ws As Worksheet
    Dim json As Object, r As String
    Set ws = ThisWorkbook.Worksheets("Sheet3")

    URL = "https://www.nseindia.com/api/option-chain-indices?symbol=BANKNIFTY"
    
    req.Open "GET", URL, False
    ' 添加禁用缓存的请求头
    req.SetRequestHeader "Cache-Control", "no-cache, no-store, must-revalidate"
    req.SetRequestHeader "Pragma", "no-cache"
    req.SetRequestHeader "Expires", "0"
    ' 添加浏览器标识，模拟合法请求
    req.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
    
    req.send
    r = req.responseText
    Set json = JsonConverter.ParseJson(r)

    ws.Range("A1").Value = json("filtered")("data")(62)("strikePrice")
    ws.Range("A2").Value = json("filtered")("data")(62)("CE")("openInterest")
    ws.Range("A3").Value = json("filtered")("data")(62)("PE")("openInterest")

    Set req = Nothing
    Set json = Nothing
    Set ws = Nothing
End Sub

关键修改说明： #

1. 禁用缓存头：Cache-Control、Pragma、Expires三个头组合使用，强制HTTP组件不使用缓存，必须从服务器拉取新数据
2. User-Agent头：模拟真实浏览器的请求标识，NSE的API会拒绝无标识的请求，或返回缓存数据
3. 清理对象：添加Set req = Nothing等语句避免内存泄漏

内容的提问来源于stack exchange，提问作者Jugal Kishor