Excel VBA如何突破API的JSON返回条数限制获取全量数据

核心逻辑 #

该API返回参数中已经包含分页必填字段：offset（当前页起始偏移量）、limit（单页返回最大条数）、total（符合条件的总记录数），属于标准的分页设计，不需要绕过限制，通过循环分批拉取所有分页即可拿到全部415条记录。
分页请求规则：

• 单页最大返回条数固定为20，单次请求的offset按20递增
• 总请求次数为向上取整(总条数/20)，你的场景共需要请求21次
• 每次拉取到数据后追加到表格，不要清空历史数据

修改后的完整VBA代码 #

' 可配合VBA-JSON库解析返回结果，也可根据自己的字符串解析方式调整
Private Const MAX_PER_PAGE As Integer = 20 ' 单页最大返回条数
Private Const BASE_API_URL As String = "你的API基础地址" ' 替换为实际API地址，不含offset、limit参数

Sub GetAllRecords()
    Dim totalRecords As Integer
    Dim currentOffset As Integer
    Dim ws As Worksheet
    Dim writeRow As Integer
    
    Set ws = ThisWorkbook.Worksheets("getLayer")
    ws.Range("A1:C1000").ClearContents ' 仅在任务开始前清空一次表格
    writeRow = 1 ' 数据写入起始行
    
    ' 第一次请求获取总记录数
    currentOffset = 0
    Dim firstResp As String
    firstResp = GetResponse(BASE_API_URL & "?offset=" & currentOffset & "&limit=" & MAX_PER_PAGE)
    ' 此处解析返回内容获取total数值，使用VBA-JSON示例代码为：
    ' Dim json As Dictionary
    ' Set json = JsonConverter.ParseJson(firstResp)
    ' totalRecords = json("total")
    ' 若自行解析字符串，可通过匹配"total":后面的数值获取总条数
    
    ' 处理第一页返回数据，对应替换为你自己的解析、写入逻辑
    ' ProcessPageData json("data"), ws, writeRow
    
    ' 循环拉取剩余分页
    currentOffset = currentOffset + MAX_PER_PAGE
    Do While currentOffset < totalRecords
        Dim resp As String
        resp = GetResponse(BASE_API_URL & "?offset=" & currentOffset & "&limit=" & MAX_PER_PAGE)
        ' 解析当前页数据并写入表格
        ' Set json = JsonConverter.ParseJson(resp)
        ' ProcessPageData json("data"), ws, writeRow
        currentOffset = currentOffset + MAX_PER_PAGE
        DoEvents ' 避免Excel无响应
        ' 可选：添加毫秒级延迟，避免请求过频被API拦截
        ' Application.Wait (Now + TimeValue("0:00:01") / 10)
    Loop
    
    MsgBox "全部" & totalRecords & "条数据拉取完成"
End Sub

' 原请求函数调整，仅负责发送请求返回响应文本，移除表格操作逻辑
Private Function GetResponse(ByVal url As String) As String
    Const RunAsync As Boolean = True
    Const ProcessComplete As Integer = 4

    Dim request As MSXML2.XMLHTTP60
    Set request = New MSXML2.XMLHTTP60
    
    With request
        .Open "GET", url, RunAsync
        .setRequestHeader "Content-Type", "application/json" ' 修正原代码中请求类型拼写错误
        .send
        
        Do While .readyState <> ProcessComplete
            DoEvents
        Loop
        
        GetResponse = .responseText
        Set request = Nothing
    End With
End Function

' 可单独封装数据写入逻辑，示例如下
' Private Sub ProcessPageData(pageData As Collection, ws As Worksheet, ByRef writeRow As Integer)
'     Dim item As Dictionary
'     For Each item In pageData
'         替换为实际字段写入逻辑
'         ws.Cells(writeRow, 1) = item("字段1")
'         ws.Cells(writeRow, 2) = item("字段2")
'         ws.Cells(writeRow, 3) = item("字段3")
'         writeRow = writeRow + 1
'     Next
' End Sub

注意事项 #

• 请根据API实际参数格式调整url拼接规则，如果原url已经有其他查询参数，分页参数用&拼接即可
• 不需要额外引入JSON解析库的场景，用字符串匹配的方式提取total字段和数据内容，不影响整体分页逻辑
• 若API有请求频率限制，可在循环中增加延迟逻辑避免被拦截

原接口返回参数参考 #

{"offset":0,"limit":20,"total":415,"

内容的提问来源于stack exchange，提问作者Rodrigo Araujo