在 VBA 中为 API 分页创建循环

问：

我正在开发一个 VBA 脚本，用于与 Excel 中的 Clockify API 进行交互。在键中，我注意到静态值被用于页面参数，将 API 响应限制为最新的 1000 条记录。detailedFilter

为了解决此限制，我想创建一个循环，该循环以迭代方式调用 API，根据给定日期范围内的记录总数 （） 调整页面参数。例如，如果是 3250，我需要使用页面值 1、2 和 3 进行 API 调用。entriesCountentriesCount

API 响应包含一个名为该字段的字段，我可以使用该字段来计算记录总数。entriesCount

下面是当前代码的片段：

Public Sub Get2223()
    
    Set httpCaller = New MSXML2.XMLHTTP60
    
    body = "{""dateRangeStart"": ""2022-06-01T00:00:00.000"", " & vbLf & _
           " ""dateRangeEnd"": ""2023-05-30T23:59:59.000"", " & vbLf & _
           " ""detailedFilter"": {""page"": 1,""pageSize"": 1000}} "
    
    httpCaller.Open "POST", "https://reports.api.clockify.me/v1/workspaces/KEY/reports/detailed"
    httpCaller.setRequestHeader "X-Api-Key", "API_KEY"
    httpCaller.setRequestHeader "Content-Type", "application/json"
    httpCaller.send body
    
    Dim json        As Object, t As Object
    Dim Data, i     As Long, N As Long
    Data = httpCaller.responseText
    
    Set json = JsonConverter.ParseJson(Data)
    N = json("timeentries").Count
    If N < 1 Then
        MsgBox "No timeentries in JSON", vbCritical
        Exit Sub
    End If
    
    Dim dataArray() As Variant
    ReDim dataArray(1 To N, 1 To 6)
    
    i = 1
    For Each t In json("timeentries")
        dataArray(i, 1) = t("projectName")
        If Not IsNull(t("taskName")) Then
            dataArray(i, 2) = t("taskName")
        End If
        dataArray(i, 3) = t("description")
        dataArray(i, 4) = t("clientName")
        dataArray(i, 5) = t("timeInterval")("start")
        dataArray(i, 6) = t("timeInterval")("duration")
        i = i + 1
    Next
    Dim ws          As Worksheet
    Set ws = Sheets("Year2022")
    
    Dim col: col = Array(1, 5, 9, 10, 11, 7)
    For i = 0 To UBound(col)
        ws.Cells(2, col(i)).Resize(N) = WorksheetFunction.Index(dataArray, 0, i + 1)
    Next
    
End Sub

有人可以帮我创建一个循环来处理这种情况下的分页吗？我感谢任何指导或建议。

我尝试了多种方法，但没有成功

 Dim httpCaller As MSXML2.XMLHTTP60, body As String
Set httpCaller = New MSXML2.XMLHTTP60
    
    ' Set your date range and initial page size
    Dim startDate As String
    Dim endDate As String
    Dim pageSize As Long

    startDate = "2022-06-01T00:00:00.000"
    endDate = "2023-05-30T23:59:59.000"
    pageSize = 1000

    body = "{""dateRangeStart"": """ & startDate & """, " & vbLf & _
           """dateRangeEnd"": """ & endDate & """, " & vbLf & _
           """detailedFilter"": {""page"": 1, ""pageSize"": " & pageSize & "}} "

    ' Parse JSON response
    Dim json As Object
    Dim Data
    Data = ""

    httpCaller.Open "POST", "https://reports.api.clockify.me/v1/workspaces/KEY/reports/detailed"
    httpCaller.setRequestHeader "X-Api-Key", "API_KEY"
    httpCaller.setRequestHeader "Content-Type", "application/json"

    ' Send the request
    httpCaller.send body

    ' Wait for the response to complete
    Do While httpCaller.readyState <> 4
        DoEvents
    Loop


    If httpCaller.Status = 200 Then

        Data = httpCaller.responseText
 
        Set json = JsonConverter.ParseJson(Data)

        Dim totalPages As Long
        totalPages = Application.WorksheetFunction.Ceiling(json("totals")("entriesCount") / pageSize, 1)

        ' Loop through additional pages
        Dim currentPage As Long
        For currentPage = 2 To totalPages
            ' Adjust the API call with the current page value
            body = Replace(body, """page"": 1", """page"": " & currentPage)

            httpCaller.send body
            Do While httpCaller.readyState <> 4
                DoEvents
            Loop

            If httpCaller.Status = 200 Then
        
                Data = httpCaller.responseText
              
                Set json = JsonConverter.ParseJson(Data)

            Else
                MsgBox "Error: " & httpCaller.Status & " - " & httpCaller.statusText, vbCritical
                Exit Sub
            End If
        Next currentPage
    Else
        MsgBox "Error: " & httpCaller.Status & " - " & httpCaller.statusText, vbCritical
    End If